Save an Excel Sheet as pdf and email

[tigerdel]

I am trying to save an Excel sheet as a pdf and then send as an email attachment but it keeps failing at the section:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= path + filename, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

I get the attached error

Sub Saveaspdfandsend()
Dim edress As String
Dim subj As String
Dim message As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim attachment As String

Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
    Set myAttachments = outlookmailitem.Attachments

path = Environ$("userprofile") & "OneDrive - D WRIGHT CONSULTING LIMITED\DWC WIP\Worksheets\"
    Application.DisplayAlerts = False
    filename = "Worksheet for period" & Format(Date, "dd mm yyyy") & ".pdf"
    subj = Sheet1.Cells(3, 5)

[COLOR=rgb(0, 0, 0)]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= path + filename, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False[/COLOR]
           
           
        attachment = path + filename
       
        outlookmailitem.To = ""
        outlookmailitem.cc = ""
        outlookmailitem.bcc = ""
        outlookmailitem.Subject = filename
        outlookmailitem.body = "Please find attached weekly Service sheet"
          
           
        myAttachments.Add (attachment)
        outlookmailitem.display
        outlookmailitem.send
        Application.DisplayAlerts = True
       
        Set outlookapp = Nothing
        Set outlookmailitem = Nothing
       
       
End Sub
[code/]
[ATTACH type="full" width="370px"]57963[/ATTACH]

Any help gratefully received

 

[gordsky]

on two occasions in your code you have path + name
this should be path & name
try changing that first and see if it works

 

[tigerdel]

Tried that and no change

 

[Fluff]

You need to add an additional \ after the environ like

path = Environ$("userprofile") & "\OneDrive - D WRIGHT CONSULTING LIMITED\DWC WIP\Worksheets\"

 

[tigerdel]

You need to add an additional \ after the environ like

path = Environ$("userprofile") & "\OneDrive - D WRIGHT CONSULTING LIMITED\DWC WIP\Worksheets\"

There is already a line in the code with path = ..... in it so I don't understand
Sorry for being thick

 

[Fluff]

But your code does not have the \ before One Drive

 

[tigerdel]

But your code does not have the \ before One Drive

Ah right - let me try that

 

[tigerdel]

OK I now get an error at myAttachments.add

 

[Fluff]

Was the pdf saved ok?

 

[tigerdel]

Ignore that - I noticed that I failed to change 1 of the + signs to an & but now I get the original error