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

New Contributor



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?

4 Replies
best response confirmed by JTOMLIN93 (New Contributor)


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
        ' To send the message immediately
    End With
    ' Optional: delete the pdf file
    Kill p & f
End Sub



That worked perfectly!!!
Could we also have it save in a file on my computer?


Yes. The macro currently uses


p = ActiveWorkbook.Path


to specify the same folder that the workbook is stored in. You can replace that with the path of a folder on your hard disk.


p = "C:\..."