# Send Email With Signature VBA (EDITING A VBA CODE)



## Youseepooo (Feb 27, 2019)

Hello, 
With some help from a fellow forum user i have this code:

1) The issues are i want it to save the file name as "x" which is *tomorrows date and save it in a network shared folder.
*
2) i also want to include a image for my company logo after my position. in the body of the email which i highlighted in blue.



Thank You Very Much!!!



> Sub Send_Email()
> Dim wPath As String, wFile As String
> Dim x As Date
> x = Format(Now() + 1, "MMMM dd, yyyy")
> ...


----------



## Domenic (Feb 27, 2019)

To embed an image within the body of an email, the image first needs to be attached to the email, and then it can be assigned to it using the HTMLBody property. Therefore, first define the folder and filename for your image...


```
Dim strPathToImageFolder As String
Dim strImageFilename As String

strPathToImageFolder = "C:\Users\Domenic\Pictures\" 'change the path accordingly
If Right(strPathToImageFolder, 1) < > "\" Then
    strPathToImageFolder = strPathToImageFolder & "\"
End If

strImageFilename = "sample.jpg" 'change the filename accordingly
```

Then, build your string, which will ultimately be assigned to the HTMLBody property...


```
******************** For the following code, remove all spaces after each _<_ symbol. ************************

Dim strHTML As String

strHTML = "<  p>Hello all,< /p>"
strHTML = strHTML & "<  p>The Daily Look Ahead Schedule for " & x & " is attached.< /p>"
strHTML = strHTML & "<  p>Thank You< br>Name< br>Position< br>< img src=""cid:" & strImageFilename & """>< br>< br>Test Sent with VBA< /p>"
```

Then attach the image to the email, in addition to attaching the PDF file...


```
With dam
    .attachments.Add wPath & wFile
    .attachments.Add strPathToImageFolder & strImageFilename
End With
```

Then, assign the html string to the email...


```
dam.htmlbody = strHTML
```

Also, just in case you're not already aware of it, when testing your code you can replace dam.send with dam.display so that the email isn't actually sent. It's only displayed. Then, when everything seems okay, you can replace dam.display with dam.send.

By the way, please use code tags [ Code]...[/Code] instead of quote tags when posting your code.  Assuming that it's formatted correctly, it will make it a lot easier to read.

Hope this helps!


----------



## Youseepooo (Feb 27, 2019)

i tried runnning it as follows but it keeps giving me an error. 


```
Dim strPathToImageFolder As StringDim strImageFilename As String


strPathToImageFolder = "C:\Users\ymussa\Pictures\Camera Roll" 'change the path accordingly
If Right(strPathToImageFolder, 1) <> "\" Then
    strPathToImageFolder = strPathToImageFolder & "\"
End If
strImageFilename = "pcc.jpg" 'change the filename accordingly


strHTML = "<p>Hello all,</p>"
strHTML = strHTML & "<p>The Daily Look Ahead Schedule for " & x & " is attached.</p>"
strHTML = strHTML & "<p>Thank You<br>Name<br>Position<br><img src=""cid:" & strImageFilename & """><br><br>Test Sent with VBA</p>"
strHTML = "<p>Hello all,</p>"
strHTML = strHTML & "<p>The Daily Look Ahead Schedule for " & x & " is attached.</p>"
strHTML = strHTML & "<p>Thank You<br>Yousef Mussa<br>Position<br><img src=""cid:" & strImageFilename & """><br><br>Test Sent with VBA</p>"
    With dam
    .attachments.Add wPath & wFile
    .attachments.Add strPathToImageFolder & strImageFilename
End With
    dam.htmlbody = strHTML
     MsgBox "Email sent"
    End Sub
```


----------



## Domenic (Feb 27, 2019)

Your macro can be amended as follows...


```
Sub Send_Email()

    Dim dam As Object
    Dim wPath As String
    Dim wFile As String
    Dim x As Date
    
    Dim strPathToImageFolder As String
    Dim strImageFilename As String
    Dim strHTML As String
    
    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
    
    strPathToImageFolder = "C:\Users\ymussa\Pictures\Camera Roll"
    If Right(strPathToImageFolder, 1) <> "\" Then
        strPathToImageFolder = strPathToImageFolder & "\"
    End If
    
    strImageFilename = "pcc.jpg"
    
    strHTML = "<p>Hello all,</p>"
    strHTML = strHTML & "<p>The Daily Look Ahead Schedule for " & x & " is attached.</p>"
    strHTML = strHTML & "<p>Thank You<br>Name<br>Position<br><img src=""cid:" & strImageFilename & """><br><br>Test Sent with VBA</p>"
    
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    
    With dam
        .To = "ymussa@gmail.com"
        .cc = "jsilko@gmail.com"
        .Subject = "Daily Schedule for " & x
        .attachments.Add wPath & wFile
        .attachments.Add strPathToImageFolder & strImageFilename
        .htmlbody = strHTML
        .Send
    End With
    
    Set dam = Nothing
        
    MsgBox "Email sent"


End Sub
```

Note that I noticed the path and filename assigned to the Filename argument of ExportAsFixedFormat was missing a backslash (\).  Accordingly, I added the backslash.

Hope this helps!


----------



## Youseepooo (Mar 5, 2019)

i would like the file to be saved in a certain location im not sure if it is actually saving it in this location:

*J:\Schedules *

ALSO the attached exported pdf is *titled* "Desktop daily look ahead" when i really want it to say " Daily look Ahead for (Tomorrows date)"
 can you help me with these last adjustments 
thanks  alot


----------



## Youseepooo (Mar 5, 2019)

> With dam    .attachments.Add wPath & wFile
> .attachments.Add strPathToImageFolder & strImageFilename End With



for that part how can i adjust to make save file name in a specific location and with tomorrows date?


----------



## Domenic (Mar 5, 2019)

Try making the following changes in red...


```
Sub Send_Email()

    Dim dam As Object
    Dim wPath As String
    Dim wFile As String
[COLOR=#ff0000]    Dim x As String[/COLOR]
    
    Dim strPathToImageFolder As String
    Dim strImageFilename As String
    Dim strHTML As String
    
    x = Format(Now() + 1, "MMMM dd, yyyy")
    
[COLOR=#ff0000]    wPath = "J:\Schedules"[/COLOR]
[COLOR=#ff0000]    wFile = "Daily Look Ahead for " & x & ".pdf"[/COLOR]
    Range("B1:I47").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & "\" & wFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    strPathToImageFolder = "C:\Users\ymussa\Pictures\Camera Roll"
    If Right(strPathToImageFolder, 1) <> "\" Then
        strPathToImageFolder = strPathToImageFolder & "\"
    End If
    
    strImageFilename = "pcc.jpg"
    
    strHTML = "< p>Hello all,</p>"
    strHTML = strHTML & "< p>The Daily Look Ahead Schedule for " & x & " is attached.</p>"
    strHTML = strHTML & "< p>Thank You< br>Name< br>Position< br><img src=""cid:" & strImageFilename & """>< br>< br>Test Sent with VBA</p>"
    
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    
    With dam
        .To = "ymussa@gmail.com"
        .cc = "jsilko@gmail.com"
        .Subject = "Daily Schedule for " & x
        .attachments.Add wPath & wFile
        .attachments.Add strPathToImageFolder & strImageFilename
        .htmlbody = strHTML
        .Send
    End With
    
    Set dam = Nothing
        
    MsgBox "Email sent"




End Sub
```


----------



## Youseepooo (Mar 5, 2019)

it wont run i get a error of not finding file. Verify path and file name are correct.. 
this code wont run and debugs on line 36 

".attachments.Add wPath & wFile"


----------



## Youseepooo (Mar 5, 2019)

its actually not running correctly i just noticed but its not attaching the specific spreadsheet. It keeps sending a different one not the actual one i am work on from my excel.


----------



## Domenic (Mar 5, 2019)

It's missing a backslash (\) . . .


```
.attachments.Add wPath & "\" & wFile
```


----------



## Youseepooo (Feb 27, 2019)

Hello, 
With some help from a fellow forum user i have this code:

1) The issues are i want it to save the file name as "x" which is *tomorrows date and save it in a network shared folder.
*
2) i also want to include a image for my company logo after my position. in the body of the email which i highlighted in blue.



Thank You Very Much!!!



> Sub Send_Email()
> Dim wPath As String, wFile As String
> Dim x As Date
> x = Format(Now() + 1, "MMMM dd, yyyy")
> ...


----------



## Youseepooo (Mar 6, 2019)

Solved!


----------



## Youseepooo (Mar 6, 2019)

is there any way to add a if statement to the variable "x". because on friday the daily report will read next days dat but techically if its friday then i need it to do it ad now()+2 instead of the regular x date.

Thanks I promise this will be the last thing  !


----------



## Domenic (Mar 6, 2019)

When the date is a Friday, now() +2 will make it a Sunday.  Is this what you want?  Or did you mean now(()+3, which would make it a Monday?


----------



## Youseepooo (Mar 7, 2019)

i meant now plus 3 sorry lol.
yea you got the idea though.


----------



## Domenic (Mar 7, 2019)

The WorkdDay method of the WorksheetFunction object can be used...


```
Dim wf As WorksheetFunction
    
    Set wf = Application.WorksheetFunction
    
    x = Format(wf.WorkDay(Date, 1), "MMMM dd, yyyy")
```

Note that the WorkDay method contains a third parameter, which allows one to exclude holidays.  Have a look at the following link for more information...

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.workday


----------

