Consulting

Results 1 to 4 of 4

Thread: VBA to Paste clipboard contents under image in email

  1. #1
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    3
    Location

    VBA to Paste clipboard contents under image in email

    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

  2. #2
    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
    Attached Images Attached Images

  3. #3
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    3
    Location
    Thanks! I will try this.

  4. #4
    You're welcome, I hope it does the job for you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •