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

[JTOMLIN93]

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?

[#2 · Accepted solution]

@JTOMLIN93 

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

[#3]

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

[#4]

@JTOMLIN93 

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:\..."

[#5]

Perfect!
Thank you so much!