+ Reply to Thread
Results 1 to 2 of 2

VBA from Excel to create an outlook email, with embedded images and table cfrom range

Hybrid View

  1. #1
    Registered User
    Join Date
    11-01-2009
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    46

    VBA from Excel to create an outlook email, with embedded images and table cfrom range

    First, I have 2 worksheets.
    The first one Sheet2 has a list of emails I want to send an email to. Then sheet1 has a list of emails most but not all would have multilpule rows that I want to create a table of column j, k, & l.

    I am trying to create an email to send to a list (from sheet2, column a), that will have some text, an image some more text another image
    more text another image, then if a column in sheet2 has a flag, a special message to this one customer, then a table from sheet1 that is filtered based on the email in sheet2 that I am working with on this loop.

    sub Calling()
        Dim x           As Long
        Dim LR          As Long
        
        Application.ScreenUpdating = False
        
        LR = Cells(Rows.count, 1).End(xlUp).row
        For x = 2 To LR
    custname = 
    EmailWithPicture(Custname As String)
    
    next
    
    end sub
    
    
    Sub EmailWithPicture(Custname As String)
     
        Dim ol As Outlook.Application
        Dim mi As Outlook.MailItem
        Dim doc As Word.Document
        Dim shp As Word.InlineShape
        Dim shp2 As Word.InlineShape
        Dim shp3 As Word.InlineShape
        Dim shp4 As Word.InlineShape
        Dim Para1 As String
    Dim Para2 As String
    Dim Para3 As String
    Dim Para4 As String
    Dim Para5 As String
    Dim Para6 As String
    Dim Para7 As String
    Dim Para8 As String
    'Dim Custname As String
    'Custname = "Customer"
    
     Dim strTable As String  ' this is for the table
    ' I am not sure how to build the table, and then insert it into the body... 
    
    
    '''-----------------
     
    Para1 = "Hello " & Custname & "," & vbNewLine & "We have received the Product!" & vbNewLine & _
    "You may receive a sales invoice that lists $.10 per item. Please disregard the amount, as we put that value on the sales order for customs purposes only. There is no charge! " & vbNewLine & vbNewLine & _
    "It is very important that you install and test this right away and give us your thoughts.!" & _
    vbNewLine & "Please note, the we hope you will enjoy the effort we put in to this!" & vbNewLine
     
     
    Para2 = vbLf & "placeholder…." & vbNewLine & _
     
    Para3 = "Placeholder 2:" & vbNewLine
     
    Para4 = "Placeholder 3." & vbLf 
     
    Para5 = "Power the unit back up." & vbLf & _
    "Please note the LEDS and how many beeps." & vbLf & _
    "The unit should connect within 15 minutes or so." & vbNewLine
     
    Para6 = "Please note, if you do NOT get any LEDS " & vbLf & _
    "If this is the case, please call in for upgrade details.." & vbNewLine & vbNewLine
    ‘ This is where I need to insert range that I need to filter based on email. Email is in column G, item is in col H, desc is in I and cost is in J
    Para7 = "Here is a list of your items ordered" & vbNewLine
     
    Para8 = "Placeholder8." & vbNewLine
     
     
        
        Set ol = New Outlook.Application
        Set mi = ol.CreateItem(olMailItem)
        
        mi.Display
        mi.To =  CustName
        mi.SentOnBehalfOfName = "support@blahblah" ‘ I may need to use the mi.ReplyRecipients.Add instead.
        Do While mi.ReplyRecipients.Count > 0
    mi.ReplyRecipients.Remove 1
    Loop
    mi.ReplyRecipients.Add [email protected]
        
        mi.Subject = "Pictures"
        
        Set doc = mi.GetInspector.WordEditor
        
            doc.Range(0, 0).InsertBefore Para8
    '----------------------------------------------------------------------
            doc.Range(0, 0).InsertBefore "List of items here...." & vbNewLine & vbNewLine  ' This is where I want the table to be inserted....
    '----------------------------------------------------------------------
    
             doc.Range(0, 0).InsertBefore Para7 '
              doc.Range(0, 0).InsertBefore vbNewLine & vbNewLine & Para6
            '_
            '"Hello " & Custname & ", " & vbNewLine & vbNewLine & Para1 & vbNewLine & vbNewLine & Para1
        Set shp4 = doc.Range(0, 0).InlineShapes.AddPicture("C:\Users\Images\Carrier3.png")
        shp4.LockAspectRatio = msoTrue
        shp4.Width = 100
            doc.Range(0, 0).InsertAfter _
           vbNewLine & Para4 & vbNewLine ' & vbNewLine & "" & vbNewLine & vbNewLine
    doc.Range(0, 0).InsertBefore vbNewLine
       '----------
        Set shp3 = doc.Range(0, 0).InlineShapes.AddPicture("C:\Users\Images\Carrier3.png")
        shp3.LockAspectRatio = msoTrue
        shp3.Width = 100
            doc.Range(0, 0).InsertAfter _
           vbNewLine & vbNewLine & Para3 & vbNewLine '& vbNewLine & "" & vbNewLine & vbNewLine
            '-------
            Set shp2 = doc.Range(0, 0).InlineShapes.AddPicture("C:\Users\Images\Carrier3.png")
        shp2.LockAspectRatio = msoTrue
        shp2.Width = 100
    doc.Range(0, 0).InsertBefore vbNewLine
        doc.Range(0, 0).InsertAfter _
           vbNewLine & Para2 '& vbNewLine & vbNewLine & "" & vbNewLine & vbNewLine
           
        Set shp = doc.Range(0, 0).InlineShapes.AddPicture("C:\Users\Images\Carrier4.png")
        shp.LockAspectRatio = msoTrue
        shp.Width = 1000
        doc.Range(0, 0).InsertBefore vbNewLine
            doc.Range(0, 0).InsertAfter _
          vbNewLine & Para1 '& vbNewLine & vbNewLine & "" & vbNewLine & vbNewLine
       
         
    doc.Range(0, 0).InsertBefore vbNewLine
     
      '  doc.Range(0, 0).InsertAfter _
       '     Para2 & vbNewLine & vbNewLine & "" & vbNewLine & vbNewLine
       ' mi.SentOnBehalfOfName = [email protected]
     
    End Sub
    Thanks!
    Mc
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-01-2009
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    46

    Re: VBA from Excel to create an outlook email, with embedded images and table cfrom range

    Hello, please disregard this post. I had to figure this out and I built a list usng tabs and text instead of tables.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Attach excel workbook embedded files (Word, PDF) in an outlook email
    By demiwen12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-25-2021, 12:09 PM
  2. Outlook VBA - Recurring Email with doc embedded into email body
    By giggles2005 in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2021, 10:07 PM
  3. [SOLVED] Send worksheet with charts and embedded images as email attachment
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2017, 08:13 PM
  4. vba to create a new outlook email with the selected excel range and charts
    By anjali_2015 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2015, 08:25 PM
  5. Embedded images in outlook email not updating
    By derpygoat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-30-2014, 01:25 PM
  6. Replies: 0
    Last Post: 07-02-2014, 03:04 PM
  7. How to send email from excel using VBA with Cell Range (Including Images) as Email Body
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2014, 05:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1