Save an Excel Sheet as pdf and email

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 

Attachments

  • 1645016675953.png
    1645016675953.png
    15.4 KB · Views: 10

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
OK so the \ disappeared and having just replaced it I get the massage at myAttachment.add
1645116245631.png
 
Upvote 0
Can you post your current code.
 
Upvote 0
So I noticed that the \ you told me about disappeared and I replaced and now I am back to the original error

Code reposted below

Rich (BB code):
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 = filename
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            path & filename, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
        attachment = path & filename
        outlookmailitem.To = ""
        outlookmailitem.cc = ""
        outlookmailitem.bcc = ""
        outlookmailitem.Subject = filename
        outlookmailitem.body = "Please find attached Weekly Service sheet"
        myAttachments.Add
        outlookmailitem.display
'        outlookmailitem.send
        Application.DisplayAlerts = True
        Set outlookapp = Nothing
        Set outlookmailitem = Nothing
End Sub
 
Upvote 0
Thanks for that. With that code does the pdf get saved?
 
Upvote 0
In that case it may have something to do with onedrive, which I've never used, so cannot help.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top