Email each sheet as a separate PDF with its sheet name as the file name.
Results 1 to 7 of 7

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

  1. #1
    New Member
    Join Date
    Aug 2017
    Location
    Oregon
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,243
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by desnyder2001 View Post
    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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Aug 2017
    Location
    Oregon
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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


    Quote Originally Posted by DanteAmor View Post
    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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,243
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by desnyder2001 View Post
    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
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Aug 2017
    Location
    Oregon
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    RESOLVED
    Wow, that was awesome. Thank you

  6. #6
    New Member
    Join Date
    Aug 2017
    Location
    Oregon
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Worked Perfect - Thank you very much

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,243
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by desnyder2001 View Post
    Worked Perfect - Thank you very much
    I'm glad to help you. I appreciate your kind comments.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •