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

[desnyder2001]

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.

 

[DanteAmor]

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:

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

 

[desnyder2001]

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:

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

 

[DanteAmor]

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:

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

 

[desnyder2001]

RESOLVED
Wow, that was awesome. Thank you

 

[desnyder2001]

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

Worked Perfect - Thank you very much

 

[DanteAmor]

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

Worked Perfect - Thank you very much

I'm glad to help you. I appreciate your kind comments.