Log in

View Full Version : VBA to Paste clipboard contents under image in email


holley
11-22-2023, 09:27 AM
Hello all! Hope you can assist. I have scrambled around and found what I need, but the code paste the clipboard contents at the beginning of the email and pushes the logo to the bottom. Any suggestions on how to keep this at the top left? As I am sure you can tell, I have copied and pasted from several different codes, any suggestions would be appreciated!


Sub PasteClipboard()Dim OutApp As Object
Dim OutMail As Object
Dim olInsp As Object
Dim MyItem As Outlook.MailItem
Dim str_jpeg_file As String
str_jpeg_file = "C:\User\New folder\Logo.jpg"
'Dim xlSheet As Worksheet
Dim wdDoc As Object
Dim oRng As Object
'Set xlSheet = ActiveWorkbook.Sheets("Unapproved")
'xlSheet.UsedRange.Copy
'On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
If Err <> 0 Then Set OutApp = CreateObject("Outlook.Application")
On Error GoTo 0
'Set OutMail = OutApp.CreateItem(0)
Set MyItem = Application.CreateItemFromTemplate("H:\Templates\Statement Customer .msg")
MyItem.Display
With MyItem
.BodyFormat = 2
.To = ""
.CC = ""
.BCC = ""
.Subject = "Customer Statement "
.Body
.Attachments.Add str_jpeg_file, 1, 0
'first we write some placeholder text so we can replace it
.HTMLBody = "##IMAGE_PLACEHOLDER##"
'replace
.HTMLBody = Replace(.HTMLBody, "##IMAGE_PLACEHOLDER##", "<img src=""cid:Logo.jpg""height=63 Width=135>")
'.Send
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.collapse 1
oRange = vbCrLf
oRng.Paste
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
End Sub
jdelano
11-30-2023, 02:33 AM
Here is a very simple example of using HTML for the body. Place your logo image on an online server. In this example I am using imgur.com and the image is from helping someone with hex colors in Excel.



Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
Dim emailBody As String


emailBody = "<html>"
emailBody = emailBody & "<table>"
emailBody = emailBody & " <tr>"
emailBody = emailBody & " <th><div style=""text-align:left""><img src='https://i.imgur.com/GyY1yml.png' width=100 height=100></div></th>"
emailBody = emailBody & " </tr>"
emailBody = emailBody & " <tr>"
emailBody = emailBody & " <td>"
emailBody = emailBody & " <p>This is the email body that we want to send in the email so the person getting the email can read it</p>"
emailBody = emailBody & " </td>"
emailBody = emailBody & " </tr>"
emailBody = emailBody & "</table>"
emailBody = emailBody & "</html>"




Set oApp = CreateObject("Outlook.Application")

Set oMail = oApp.CreateItem(olMailItem)
On Error Resume Next
With oMail
.To = ***** the email address to send the email to **********
.Subject = "Test HTML Email with Logo"
.HTMLBody = emailBody

If Err = 0 Then
' send if the previous commands ran ok
.Send
End If

End With
Set oMail = Nothing


oApp.Quit
Set oApp = Nothing




The email as it is received
holley
11-30-2023, 09:18 AM
Thanks! I will try this.
jdelano
11-30-2023, 10:41 AM
You're welcome, I hope it does the job for you.