Send Email With Signature VBA (EDITING A VBA CODE)

[Youseepooo]

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")
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@gmail.com"
dam.cc = "jsilko@gmail.com"
dam.Subject = "Daily Schedule for " & x
dam.body = "Hello all," & vbNewLine & vbNewLine _
& "The Daily Look Ahead Schedule for " & x & " is attached." & vbNewLine & vbNewLine & vbNewLine & vbNewLine _
& "Thank You" & vbNewLine & "Name" & vbNewLine & "Position" & vbNewLine & "Test Sent with VBA"
dam.Attachments.Add wPath & wFile
dam.Send
MsgBox "Email sent"




End Sub

 

[Domenic]

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]

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]

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]

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]

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]

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]

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]

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]

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

.attachments.Add wPath & "\" & wFile