Emails A PDF Created From An Excel File - Help!!

[TkdKidSnake]

Hi all,

I am trying to set several PDF files to send automatically and so far I have only managed to create the sheet as a PDF successfully and am completely bamboozled on how to open a new email window attached the file ect ect - see below:

The code I have is as below and this creates the PDF and saves it in the Test Folder

Sheets("Supplier1").Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\ss7\Desktop\TestFolder\" & Range("AE4").Value, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

o What I want to do now is open a new email window attached the created file to the email
The filename is on the same sheet in cell AE4 accept would have .PDF at the end.

o Subject of the email also needs to be the filename

o I also want to add the recipients in both the To and CC boxes
I have created an “Emails” sheet in the same workbook
For supplier 1 the TO email is in cell C3
The CC email address is in cell C49

o The body off the email also needs to be as below:

-----------------------------------------------------------------------------------------------------------------------------------

Dear Supplier


Attached is your latest Scorecard which has now been updated to include all the relevant data for previous month.
please review and contact myself or any member of the management team here at HydraForce if you want to discuss further.


Thank you for your continued support.

-----------------------------------------------------------------------------------------------------------------------------------

o I would like the email to display on the screen and not to send automatically

However if the you could add the code to send automatically but block it out then once I am comfortable that it is doing exactly what I want then I will change it


Any help you can provide me with on this would be greatly appreciated.


Many thanks.

 

[Imabus]

The place i always advise starting for emailing from excel is https://www.rondebruin.nl/index.htm. There are dozens of examples of how to email your data in a variety of ways.

i used the code from there and edited it with what you described above.

Dim OutApp As Object
    Dim OutMail As Object
    Dim fname As String, sendto As String, sendcc As String, sendbody As String


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Check sheet names, paths, cell locations and email text below
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    fname = "C:\Users\ss7\Desktop\TestFolder\" & Range("AE4").Value
    sendto = Sheets("Emails").Range("C3").value
    sendcc = Sheets("Emails").Range("C49").value
    sendbody = "Dear Supplier" & Chr(13) & "Attached is your latest Scorecard which has now been updated to include all" _
    & " the relevant data for previous month. Please review and contact myself or any member of the management team here " _
    & "at HydraForce if you want to discuss further." & Chr(13) & "Thank you for your continued support."
    
    Sheets("Supplier1").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = sendto
        .CC = sendcc
        .BCC = ""
        .Subject = fname
        .Body = sendbody
        .Attachments.Add fname & ".pdf"
        .Display
        '.send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

 

[TkdKidSnake]

Hi Imabus,
Many thanks for your help I will give it a go to see how it works, I'm very new to this and have tried different ways to try to get it to work.

Much appreciated

 

[TkdKidSnake]

This is excellent and works a treat many thanks for all your help its greatly appreciated.

The place i always advise starting for emailing from excel is https://www.rondebruin.nl/index.htm. There are dozens of examples of how to email your data in a variety of ways.

i used the code from there and edited it with what you described above.

Dim OutApp As Object
    Dim OutMail As Object
    Dim fname As String, sendto As String, sendcc As String, sendbody As String


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Check sheet names, paths, cell locations and email text below
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    fname = "C:\Users\ss7\Desktop\TestFolder\" & Range("AE4").Value
    sendto = Sheets("Emails").Range("C3").value
    sendcc = Sheets("Emails").Range("C49").value
    sendbody = "Dear Supplier" & Chr(13) & "Attached is your latest Scorecard which has now been updated to include all" _
    & " the relevant data for previous month. Please review and contact myself or any member of the management team here " _
    & "at HydraForce if you want to discuss further." & Chr(13) & "Thank you for your continued support."
    
    Sheets("Supplier1").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = sendto
        .CC = sendcc
        .BCC = ""
        .Subject = fname
        .Body = sendbody
        .Attachments.Add fname & ".pdf"
        .Display
        '.send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

 

[Imabus]

This is excellent and works a treat many thanks for all your help its greatly appreciated.

No problem at all

I find that email bit of code incredibly useful!