Add additional comments to a Outlook Template email

[rplohocky]

Hello,
I have a macro that grabs a specific email template then attaches a specific PDF file to it then sends it all off without display.
Here's the code:
<code>
Dim objOL As Outlook.Application, msg As MailItem, p$, i%, ap$

'Below is the location of the all the workflows on the network
ap = "\\fleet.ad\Data\Data1\VMSSHARE\Team GE Workflows"
Set objOL = CreateObject("Outlook.Application")
On Error Resume Next
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row 'the letter B is what the code looks at to choose the correct template. B must be changed in 3 places
Select Case UCase(Left(Cells(i, "B"), 100)) 'this number represent the amount of charactors in each case line. example ASSIGN VEHICLE ONLY is 19 charactors
Case "ASSIGN VEHICLE ONLY": p = "ASSIGN VEHICLE ONLY" 'This is one of 3 cases. the name just after the word CASE must match the template name.
Case "ASSIGN VEHICLE AND TITLE WORK": p = "ASSIGN VEHICLE AND TITLE WORK" 'You can create as many cases as you want
Case "DRIVER ADD": p = "DRIVER ADD"
Case "DISCONTINUE UNASSIGN": p = "DISCONTINUE UNASSIGN"
Case "DISCONTINUE TERM": p = "DISCONTINUE TERM"
Case "RENTAL UNIT WITH ENTERPRISE": p = "RENTAL UNIT WITH ENTERPRISE"
Case "DISCONTINUE MOVE TO STORAGE": p = "DISCONTINUE MOVE TO STORAGE"
Case "ASSIGN VEHICLE AND MOVE AND TITLE WORK": p = "ASSIGN VEHICLE AND MOVE AND TITLE WORK"
End Select

'Below is the location of all the templates. Add new templates to this locations if needed.
Set msg = objOL.CreateItemFromTemplate("\\fleet.ad\Data\Data1\VMSSHARE\Team GE Workflows\Work Flow Templates" & p & ".msg") 'the p represents the template name

If Err.Number > 0 Then MsgBox "No such template", vbCritical, Cells(i, "B") & "/Error #" & Err.Number 'warning box

'Below is the subject line. Each number represents columns, Example: 1 = column A.
msg.Subject = Cells(i, 2) & " Workflow# " & Cells(i, 1) & " Fleet/Unit " & _
Cells(i, 5) & " / " & Cells(i, 6)
msg.Attachments.Add ap & Cells(i, 1) & ".pdf" 'This looks at the WF number and attaches the WF based on Column A
msg.Send 'Send 'display Change this if you want to display email be its sent
Err.Clear
Next
Set msg = Nothing
Set objOL = Nothing
</code>

What i would like to add is the ability to add unique comments to each of these templates before they send and still keep what's already in the body of the template.

I know how to add "msg.Body = Cells(i, 3)" to this code but it overwrites what is already in the email template. I want to keep what's in there and add a unique comment at will.

Right now this macro looks at specific cells and grabs data from them. I can make column C be the comments column so it can be grabbed and added to the template Any idea how this can be done?

 

[Mister H]

Hi

I am not sure if I am understanding you correctly but I am thinking that all you should do is DISPLAY the email instead of SENDING it.

Change this line of code:

[FONT=Courier New]msg.Send  'Send  'display Change this if you want to display email be its sent[/FONT]

TO:

[FONT=Courier New]msg.Display  'Send  'display Change this if you want to display email be its sent[/FONT]

I think that will the create your email and display it. You should be able to EDIT it before clicking send. If that is not what you are looking for then just write back

Good Luck,
Mark

 

[rplohocky]

Hi

I am not sure if I am understanding you correctly but I am thinking that all you should do is DISPLAY the email instead of SENDING it.

Change this line of code:

[FONT=Courier New]msg.Send  'Send  'display Change this if you want to display email be its sent[/FONT]

TO:

[FONT=Courier New]msg.Display  'Send  'display Change this if you want to display email be its sent[/FONT]

I think that will the create your email and display it. You should be able to EDIT it before clicking send. If that is not what you are looking for then just write back

Good Luck,
Mark

Hello Mister H,
Thanks for responding. I know switching send to display will open each email before its sent but I want to do something different.

The way the macro works right now is it grabs data from specific cells and inserts them into various areas such as cell A1 has a number in it and inserts it into the subject line of one of the email templates.

I want to make column C the area that I can type in special comments and have the macro add these comments to the email template without overwriting the verbiage on the email template. Right now if I add "msg.Body = Cell(i, 3)" this will look in column C and replace the body of the email with whatever is in column C. I just want to add to the body not replace.