PDA

View Full Version : Generate Outlook E-mail via Word using VBA, Formatting Issue



mcroth
05-27-2015, 10:23 AM
Hello,

I have an excel sheet that creates a table, of which I only need a few rows (starting from the top). I am trying to copy the rows I need from the table along with some text to outlook via word using VBA. I'm using word because it is very easy to copy the chart I need and keep the formatting. the e-mail I'm trying to create is set up like this:

Name,

"lineone"

Paste Range (table)

"linetwo"

"linethree"

I've successfully set up this format with no problems and everything looks good. My issue is that I would like to make "linetwo" bold and "linethree" red.

I know it is possible to do this with wddoc.range(start,end).bold but I'm having a hard time defining the start because I don't know how to find the length of my pasted range.

I need something that can either define the length of my pasted range or can identify "linetwo" and make it bold and "linethree" and make it red.

My current code is below:

Sub Email()
Sheets("E-Mail Generator").Select
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim OlInsp As Outlook.Inspector
Dim wdDoc As Word.Document
Let srange = "B1" & ":" & "F" & Sheets("E-Mail Generator").Range("I3")
Set rng = Sheets("E-Mail Generator").Range(srange)
amount = Sheets("E-Mail Generator").Range("N2")
fin = Sheets("E-Mail Generator").Range("Q2")
lineone = "Dear [Name]," & vbNewLine & vbNewLine & "Per your request, we would recommend the following:"
Lastday = Sheets("E-Mail Generator").Range("S2")
linetwo = " " & vbNewLine & "*Dated" & Lastday & vbNewLine & vbNewLine
linethree = "say something really cool"
Set OutApp = New Outlook.Application
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.BodyFormat = olFormatHTML
.Display
.To = "teammail"
.CC = ""
.BCC = ""
.Subject = "Lets get info - (Num " & fin & ")"

Set OlInsp = .GetInspector
Set wdDoc = OlInsp.WordEditor

wdDoc.Range.InsertBefore lineone & vbNewLine & " " & linetwo & linethree
Range(srange).Copy
wdDoc.Range(Len(lineone), Len(lineone)).Paste
End With
End Sub