EXCEL VBA MACRO TO email SHEET AS PDF ATTACHMENT

[Youseepooo]

I want to do as the title states.
Print a certain range as pdf and send as attachment with a choice of a body.
Can someone help me ?


Thanks
YM

 

[DanteAmor]

If it's by outlook.

Change C1:H14 by the range.
In the data in red put your data

Sub Send_Email()
    Dim wPath As String, wFile As String
    
    wPath = ThisWorkbook.Path
    wFile = "Filepdf.pdf"
    Range("[COLOR=#ff0000]C1:H14[/COLOR]").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False


    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    '
    dam.To = "[COLOR=#ff0000]email@gmail.com[/COLOR]"
    dam.Subject = "[COLOR=#ff0000]email subject[/COLOR]"
    dam.Body = "[COLOR=#ff0000]email body[/COLOR]"
    dam.Attachments.Add wPath & wFile
    dam.Send
    MsgBox "Email sent"


End Sub

 

[Youseepooo]

thanks this works great!

 

[DanteAmor]

You're welcome and thanks for the feedback.

 

[Youseepooo]

Is there a way to adjust this to make a box appear asking to save as a file name. I use it for Schedule purposes. I need it to save everyday with the date as the file name. Thanks Dante!

 

[Youseepooo]

Actually i adjusted it myself, i need some more help if you can though...

Right now the code is working fine however
1) I would like the File name of the saved PDF to be saved in a shared network for a company i work for. (J:\Schedules\PCC Daily Schedule\2019 February) I also would like the file name to be dated as per the following day.
2) My code right now sends with today's date but i would like to know how to edit the format operation to make it always say the next days date in the file name, and in subject, and in body.

3) is there a way to include my signature with the email, since it includes all my contact info.

4) just to clarify.... i want to know how to edit my code for it to include saving the file in a specific location in a shared network. and i want it to send the email with the following days date as the file name, subject, and in the body of email!!!

Thanks to whoever is able to guide me

Sub Send_Email()
    Dim wPath As String, wFile As String
    Dim x As Date
    x = Format(Now(), "MMMM dd, yyyy")
    wPath = ThisWorkbook.Path
    wFile = "Daily Look Ahead.pdf"
    Range("a1:H47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False




    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    '
    dam.To = "ymussa@perfettocontracting.com"
    dam.Subject = "Daily Schedule for " & x
    dam.body = "Hello all, The Daily Look Ahead Schedule for " & x & " is attached."
    dam.Attachments.Add wPath & wFile
    dam.Send
    MsgBox "Email sent"




End Sub

 

[Youseepooo]

SO i figured one more thing out haha, tomorrows date is just now() +1 so this works. my only problem is how to get my pdf file to save in a network location and save under my specific date variable which is "x" for me.

this is my current code...

Sub Send_Email()
Dim wPath As String, wFile As String
Dim x As Date
x = Format(Now() + 1, "MMMM dd, yyyy")
wPath = ThisWorkbook.Path
wFile = "Daily Look Ahead.pdf"
Range("b1:i47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False




Set dam = CreateObject("Outlook.Application").CreateItem(0)
'
dam.To = "ymussa@perfettocontracting.com"
dam.Subject = "Daily Schedule for " & x
dam.body = "Hello all, The Daily Look Ahead Schedule for " & x & " is attached."
dam.Attachments.Add wPath & wFile
dam.Send
MsgBox "Email sent"




End Sub

 

[Ivy_1011]

Actually i adjusted it myself, i need some more help if you can though...

Right now the code is working fine however
1) I would like the File name of the saved PDF to be saved in a shared network for a company i work for. (J:\Schedules\PCC Daily Schedule\2019 February) I also would like the file name to be dated as per the following day.
2) My code right now sends with today's date but i would like to know how to edit the format operation to make it always say the next days date in the file name, and in subject, and in body.

3) is there a way to include my signature with the email, since it includes all my contact info.

4) just to clarify.... i want to know how to edit my code for it to include saving the file in a specific location in a shared network. and i want it to send the email with the following days date as the file name, subject, and in the body of email!!!

Thanks to whoever is able to guide me

Sub Send_Email()
    Dim wPath As String, wFile As String
    Dim x As Date
    x = Format(Now(), "MMMM dd, yyyy")
    wPath = ThisWorkbook.Path
    wFile = "Daily Look Ahead.pdf"
    Range("a1:H47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False




    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    '
    dam.To = "ymussa@perfettocontracting.com"
    dam.Subject = "Daily Schedule for " & x
    dam.body = "Hello all, The Daily Look Ahead Schedule for " & x & " is attached."
    dam.Attachments.Add wPath & wFile
    dam.Send
    MsgBox "Email sent"




End Sub

I have used this code and it works really well. I would like to name the file based on a cell or range. How would I go about being able to create this. Instead of Filename:=wPath & wFile; How can I name it based on Cell or Range

Range("a1:H47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _

 

[DanteAmor]

Change this:

    wPath = ThisWorkbook.Path & "\"
    wFile = "Daily Look Ahead.pdf"

For this:

    wPath = ThisWorkbook.Path & "\"
    wFile = Range("A2").value & ".pdf"

 

[Ivy_1011]

Change this:

    wPath = ThisWorkbook.Path & "\"
    wFile = "Daily Look Ahead.pdf"

For this:

    wPath = ThisWorkbook.Path & "\"
    wFile = Range("A2").value & ".pdf"

@DanteAmor This worked perfectly, thank you!