# EXCEL VBA MACRO TO email SHEET AS PDF ATTACHMENT



## Youseepooo (Feb 25, 2019)

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 (Feb 25, 2019)

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 (Feb 25, 2019)

thanks this works great!


----------



## DanteAmor (Feb 25, 2019)

You're welcome and thanks for the feedback.


----------



## Youseepooo (Feb 26, 2019)

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 (Feb 26, 2019)

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 (Feb 26, 2019)

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")
> ...


----------



## Ivy_1011 (Jul 22, 2021)

Youseepooo said:


> 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.
> ...


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 (Jul 22, 2021)

Change this:


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

For this:


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


----------



## Ivy_1011 (Jul 23, 2021)

DanteAmor said:


> Change this:
> 
> 
> ```
> ...


@DanteAmor This worked perfectly, thank you!


----------



## Youseepooo (Feb 25, 2019)

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 (Jul 23, 2021)

Ivy_1011 said:


> This worked perfectly, thank you!


Again with pleasure. Thanks for the feedback


----------

