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
Bookmarks