VBA Code - Create PDF and attach to email macro not working.

[Panakin]

Hi all,

I've used this VBA code before with Excel 2003 and it worked flawlessly.
I am now using Excel 2016 and it debugs at ".Attachments.Add PDF_File" stage.

The PDF is being created in the correct location, the emailed is attempted to be opened with the warning window I created popping up. But alas the email doesn't get created or sent as it debugs.
I coloured in the line that debugs with red text.

Would love some help!
Thank you.
____________________________

Sub MondayDAYPDF()

  Dim olApp As Object
 
  Path = "file location"
  Salesman = ActiveSheet.Name & " Day Shift"
  strDate = Format(Date, "yyyymmdd")
  If i > 1 Then PDF_File = Left(PDF_File, i - 1)
  PDF_File = Path & strDate & Salesman & ".pdf"

 With Worksheets("Monday Shift Report").PageSetup
     .Orientation = xlPortrait
     End With
       
Sheets("Monday Shift Report").Range("A1:M56").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_File, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
  
    
 With Worksheets("Monday Shift Report").PageSetup
       .PrintArea = "A1:M56"
       .Zoom = False
       .FitToPagesTall = 1
       .FitToPagesWide = 1
       End With
    
     If MsgBox("Are you sure you are ready to send the shift report? If you are then please ensure Outlook is open before pressing OK. After sending, a confirmation box will show - if you do not see this box the e-mail may not have sent.", vbOK) = vbOK Then
    'code to send message
Else
    Exit Sub 'terminate macro
End If
    
Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "HR Operations Monday Day Shift Report"
            .To = "destination email"
            .Body = "HR Operations Monday Day Shift Report attached" & Chr(13) & Chr(13) & "Kind regards"
            [COLOR=#ff0000].Attachments.Add PDF_File[/COLOR]
            .Send
        End With
    
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing

End Sub

 

[Logit]

My thoughts are ... the issue is located within this portion of your macro :

Path = "file location"
  Salesman = ActiveSheet.Name & " Day Shift"
  strDate = Format(Date, "yyyymmdd")
  If i > 1 Then PDF_File = Left(PDF_File, i - 1)
  PDF_File = Path & strDate & Salesman & ".pdf"

This line in particular :

PDF_File = Path & strDate & Salesman & ".pdf"

is most likely not creating the correct path for your macro to 'read / interpret '.

This is "air code" but maybe something along the lines of :

Path & "\" & strDate & "\" & Salesman & ".pdf"

Try elimating this from the last line noted above : "Path & strDate & Salesman & ".pdf" ... then hard code the path for one of your salesmen. Something like :
"C:\Users\Documents\20170223JoeSmith.pdf" ... then see if it sends the email. (NOTE: the suggested path is only 'suggested', you'll need use a real path for your project).
If the email goes with the hard coded version, that would tend to indicate the error is located on the last line noted above.

Hope that makes sense.

 

[Panakin]

Firstly, thank you for your help Logit.

I have added this string into my macro and it has now moved the issue to a new string which I have once again coloured red.
Any ideas what the issue could be now?
Runtime error '1004' is also created when trying to run the macro.

[LEFT][COLOR=#333333][FONT=monospace]Sub MondayDAYPDF()

  Dim olApp As Object
 
  Path = "file location"
  Salesman = ActiveSheet.Name & " Day Shift"
  strDate = Format(Date, "yyyymmdd")
  If i > 1 Then PDF_File = Left(PDF_File, i - 1)
  PDF_File = [LEFT][COLOR=#333333][FONT=monospace]Path & "\" & strDate & "\" & Salesman & ".pdf"[/FONT][/COLOR][/LEFT]

 With Worksheets("Monday Shift Report").PageSetup
     .Orientation = xlPortrait
     End With
       
[COLOR=#ff0000]Sheets("Monday Shift Report").Range("A1:M56").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_File, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False[/COLOR]
  
    
 With Worksheets("Monday Shift Report").PageSetup
       .PrintArea = "A1:M56"
       .Zoom = False
       .FitToPagesTall = 1
       .FitToPagesWide = 1
       End With
    
     If MsgBox("Are you sure you are ready to send the shift report? If you are then please ensure Outlook is open before pressing OK. After sending, a confirmation box will show - if you do not see this box the e-mail may not have sent.", vbOK) = vbOK Then
    'code to send message
Else
    Exit Sub 'terminate macro
End If
    
Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "HR Operations Monday Day Shift Report"
            .To = "destination email"
            .Body = "HR Operations Monday Day Shift Report attached" & Chr(13) & Chr(13) & "Kind regards"
            .Attachments.Add PDF_File[/COLOR][/FONT][/LEFT]
[COLOR=#333333][FONT=monospace]
            .Send
        End With
    
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing

End Sub[/FONT][/COLOR]

 

[Panakin]

Good morning all,

Any further help available?

Thank you!

 

[Logit]

.
It is easier to debug with the actual workbook. Would you be willing to post it for review ? Make certain nothing private or confidential is included.

 

[Panakin]

Hi Logit,

I have isolated the 2 sheets needed into a separate work book and edited anything private out.

Unfortunately, I don't have the required permissions to attach files.
Do you know when this feature is unlocked and have any suggestions how to publicise the workbook in the short term?
Could I email it to you directly?

Regards,

 

[Logit]

This forum does not have provision for posting workbooks. Only your sample code.

You can up load your project to a Cloud site (Amazon Drive, Google, DropBox, etc.). Then post the link here.