Save an Excel Sheet as pdf and email

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
135
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

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Was the pdf saved ok?
With the plus sign there Yes it was but when I changed the plus sign in the attachment line it failed at the ActiveSheet line just above it
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
135
Office Version
  1. 365
Platform
  1. Windows
OK so the \ disappeared and having just replaced it I get the massage at myAttachment.add
1645116245631.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,713
Office Version
  1. 365
Platform
  1. Windows
Can you post your current code.
 

tigerdel

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,713
Office Version
  1. 365
Platform
  1. Windows
Thanks for that. With that code does the pdf get saved?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,713
Office Version
  1. 365
Platform
  1. Windows
In that case it may have something to do with onedrive, which I've never used, so cannot help.
 

Forum statistics

Threads
1,176,137
Messages
5,901,567
Members
434,906
Latest member
Prabhu_Churi

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
Top