VBA save as PDF and attach PDF to email and send

[ALien0521]

This is my first post so bear with me. I'm attempting to create one button that will save my spreadsheet as a PDF in a designated location and then take that same pdf just saved and attach and email it all in one go. Currently it is saving correctly and emailing. It just is not attaching the pdf to the email. I'm very new to VBA as well

Private Sub Email_Click()


    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY"), _
            OpenAfterPublish:=False
    Application.ScreenUpdating = True


'Updated by Extendoffice 2017/9/14
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = ""
    
    
       Dim strFilePath As String
   Dim strFileName As String
   
   strFilePath = "O:\Adam\Excel\"
   strFileName = "MOD Report" & Format(Now(), "DD-MMM-YYYY")
    
                  On Error Resume Next
    With xOutMail
        .To = "adam.lien@mayociviccenter.com"
        .CC = ""
        .BCC = ""
        .Subject = "MOD Report"
        .Body = xMailBody
        .Attach = strFilePath & "\" & strFileName
        .Send   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing


End Sub

 

[Trevor_S]

You're creating the filename twice, and I think you've used a space in one, but not the other:
When creating the PDF, you use:

Filename:="O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY")
​

but when you're creating the email you use:

strFileName = "MOD Report" & Format(Now(), "DD-MMM-YYYY")
​

​

The first has a space between "Report" and the date, the latter does not.

I would suggest creating the filename once, putting it into a variable, and use that same variable both times:
​

Private Sub Email_Click()


    Application.ScreenUpdating = False
    myFilename = "O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY") 'setup filename for use in line after next and as attachment 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:= myFilename, _
            OpenAfterPublish:=False
    Application.ScreenUpdating = True


'Updated by Extendoffice 2017/9/14
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = ""
    
    
       Dim strFilePath As String
   Dim strFileName As String
   
   'Next two lines no longer required
   'strFilePath = "O:\Adam\Excel\"
   'strFileName = "MOD Report" & Format(Now(), "DD-MMM-YYYY")
    
                  On Error Resume Next
    With xOutMail
        .To = "adam.lien@mayociviccenter.com"
        .CC = ""
        .BCC = ""
        .Subject = "MOD Report"
        .Body = xMailBody
        .Attach = myFilename
        .Send   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing


End Sub
​

This also gets round the (theoretical) problem that you may have had if you ran the macro at midnight, and the date changed between saving the pdf and sending the email!!

 

[ALien0521]

Aww I see, thanks for catching that bit of sloppy programming.

I'm still having issues with the file attaching however. It saves correctly still and still sends the email but without the attachment.

Thanks, again for your help!

 

[Trevor_S]

Try changing this line:

        .Attach = myFilename

to:

        .Attachments.Add myFilename

 

[ALien0521]

Still not working. Here's the code now with the adjustments you've suggested just to make sure I'm on the same page.

Private Sub Email_Click()




    Application.ScreenUpdating = False
    myFilename = "O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY") 'setup filename for use in line after next and as attachment
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=myFilename, _
            OpenAfterPublish:=False
    Application.ScreenUpdating = True




'Updated by Extendoffice 2017/9/14
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = ""
    
       Dim strFilePath As String
   Dim strFileName As String
    
                  On Error Resume Next
    With xOutMail
        .To = "adam.lien@mayociviccenter.com"
        .CC = ""
        .BCC = ""
        .Subject = "MOD Report"
        .Body = xMailBody
        .Attachments.Add myFilename
        .Send   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing




End Sub

Thanks

 

[daverunt]

Hi,

myFilename must be the path\filename.pdf
Although the export process creates a .pdf file, the variable doesn't contain the extension, you need to add that.

e.g myFileName = MOD Report 30-May-2018

So you can add it as below or at the Attachments .Add stage

myFilename = "O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY") & ".pdf"

or

.Attachments.Add myFilename & ".pdf"

You can see this if you step through your code using F8 - hover the mouse over the variables once you step past the line they are on. The content of the variable will be shown in a tooltip.

 

[Trevor_S]

Think that I may have spotted it. This line doesn't show the filetype:

myFilename = "O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY")

If excel is adding the .PDF to the filename when it creates it, outlook won't be able to find the filename which is stored without it.
Try changing to this:

myFilename = "O:\Adam\Excel\MOD Report " & Format(Now(), "DD-MMM-YYYY") & ". PDF"

 

[ALien0521]

Thanks all, it works perfectly now. I appreciate the help!