EXCEL VBA MACRO TO email SHEET AS PDF ATTACHMENT

Youseepooo

New Member
Joined
Feb 5, 2019
Messages
37
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If it's by outlook.

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


Code:
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

New Member
Joined
Feb 5, 2019
Messages
37

ADVERTISEMENT

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

New Member
Joined
Feb 5, 2019
Messages
37
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 :)



Code:
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

New Member
Joined
Feb 5, 2019
Messages
37
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,388
Messages
5,528,408
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top