Macro for saving sheet as pdf using cell value as name and then emailing the pdf via outlook

Hi,

Im really struggling to get a macro for the below, any help is really appreciated!

I want to save the sheet as a pdf but using the value in 'F4' as the name. I then want to send that pdf as a email via outlook.

I also have a macro running that means every time the sheet is opened the invoice number changes, the invoice number is in 'F4'

Anyone have any suggestions?

Try this. See the comments in the code. If you want to send the message without intervention, it works best if Outlook is already running, otherwise the message might remain in the Outbox.

Sub Saveaspdfandsend()
    Dim w As Worksheet ' Active sheet
    Dim p As String    ' Path
    Dim f As String    ' Filename
    Dim o As Object    ' Outlook application
    Dim m As Object    ' Outlook mailItem

    Set w = ActiveSheet
    p = ActiveWorkbook.Path
    If Right(p, 1) <> "\" Then
        p = p & "\"
    End If
    f = w.Range("F4").Value & ".pdf"
    w.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & f
    Set o = CreateObject(Class:="Outlook.Application")
    Set m = o.CreateItem(0)
    With m
        .To = "someone"
        .Subject = "Sending " & f
        .Body = "Please find the file " & f & " attsched."
        .Attachments.Add p & f
        ' Use only one of the two instructions below
        ' To inspect the message and send it manually
        .Display
        ' To send the message immediately
        .Send
    End With
    ' Optional: delete the pdf file
    Kill p & f
End Sub

4 Replies