Email each sheet as a separate PDF with its sheet name as the file name.

desnyder2001

New Member
Joined
Aug 4, 2017
Messages
12
I am trying to create a 1 Click button that will attach each sheet as a separate PDF file with the sheet name as the file name to an outlook email.
I am currently able to accomplish the above but it merges all the files into 1 PDF. They need to be separate.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I am trying to create a 1 Click button that will attach each sheet as a separate PDF file with the sheet name as the file name to an outlook email.
I am currently able to accomplish the above but it merges all the files into 1 PDF. They need to be separate.


Try this:

Code:
Sub Macro6()
    Dim sh As Worksheet, wPath As String, wFile As String
    wPath = ThisWorkbook.Path & "\"
    For Each sh In Sheets
        wFile = wPath & sh.Name & ".pdf"
        sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wFile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Set dam = CreateObject("Outlook.Application").CreateItem(0)
        dam.To = "email@google.com"
        dam.Subject = "Subject"
        dam.Body = "body"
        dam.Attachments.Add wFile
        dam.Display
    Next
End Sub
 
Upvote 0
This worked exactly as you wrote it. Is there anyway that all the attachments can be in one email instead of an email for each attachment? That would oh so awesome
Thank you either way because if nothing else this is going to save a ton of time


Try this:

Code:
Sub Macro6()
    Dim sh As Worksheet, wPath As String, wFile As String
    wPath = ThisWorkbook.Path & "\"
    For Each sh In Sheets
        wFile = wPath & sh.Name & ".pdf"
        sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wFile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Set dam = CreateObject("Outlook.Application").CreateItem(0)
        dam.To = "email@google.com"
        dam.Subject = "Subject"
        dam.Body = "body"
        dam.Attachments.Add wFile
        dam.Display
    Next
End Sub
 
Upvote 0
This worked exactly as you wrote it. Is there anyway that all the attachments can be in one email instead of an email for each attachment? That would oh so awesome
Thank you either way because if nothing else this is going to save a ton of time


Try this:

Code:
Sub Macro6()
    Dim sh As Worksheet, wPath As String, wFile As String
    wPath = ThisWorkbook.Path & "\"
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    dam.To = "email@goole.com"
    dam.Subject = "Subject"
    dam.Body = "body"
    For Each sh In Sheets
        wFile = wPath & sh.Name & ".pdf"
        sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wFile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        dam.Attachments.Add wFile
    Next
    dam.Display
End Sub
 
Upvote 0
RESOLVED: Email each sheet as a separate PDF with its sheet name as the file name.

Worked Perfect - Thank you very much
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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