forward email with attachment using .oft

[dwebbcw]

I have no experience but I've cannibalized/pieced together a vba Outlook macro that calls an outlook .oft template. The end result is that the user is prompted for entries like firstname, lastname, etc. which are added into the body of the email along with some static text. My end users are receiving an email from the scanner with a pdf attached. Rather than activating the macro (below) which creates a new email which they have to drag the pdf into, I would like them to be able to just run the macro directly on the email from the scanner so they don't have to bother finding the first email and dragging the pdf over to the new email.

How can I get this macro to forward an existing email rather than creating a new one?

Sub Statement_Email()
Dim myItem As Outlook.MailItem
Dim strRecipient As String
Dim strStatementMonth As String
Dim strThroughDate As String
Dim strHTML As String


Set myItem = Application.CreateItemFromTemplate("C:\Users\username\AppData\Roaming\Microsoft\Templates\Statement.oft")
strHTML = myItem.HTMLBody

strLastName = InputBox("Recipients Last Name?")
strFirstName = InputBox("Recipients First Name?")
strPrefix = InputBox("Recipients Prefix (ex. Mr., Ms., Dr.)?")
strMatterID = InputBox("Matter Number?")
strStatementMonth = InputBox("What is the statement date?")
strThroughDate = InputBox("Services rendered through what date?")

myItem.HTMLBody = Replace(myItem.HTMLBody, "%STATEMENTMONTH%", strStatementMonth)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%THROUGHDATE%", strThroughDate)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%RECIPIENT%", strRecipient)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%PREFIX%", strPrefix)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%LASTNAME%", strLastName)
myItem.Subject = Replace(myItem.Subject, "%LASTNAME%", strLastName)
myItem.Subject = Replace(myItem.Subject, "%FIRSTNAME%", strFirstName)
myItem.Subject = Replace(myItem.Subject, "%MATTERID%", strMatterID)
myItem.Display

End Sub

 

[Diane Poremsky]

This line: Set myItem = Application.CreateItemFromTemplate("C:\Users\username\AppData\Roaming\Microsoft\Templates\Statement.oft")

control what happens.

Assuming the message is selected, not open on screen, use
Dim oItem As Outlook.MailItem
Set oItem= Session.ActiveExplorer.Selection.Item(1)
set myitem = oitem.forward

 

[dwebbcw]

Thank you Diane.

I added those lines but am still having a problem. When the email with the attachment is highlighted in my inbox I click the quick launch icon to run the macro. I get an error that reads:
Run-time error '438': Object doesn't support this property or method.

Maybe I didn't add your lines correctly, this is what the entire macro looks like now:

Sub Forward_Statement2()
Dim myItem As Outlook.MailItem
Dim strRecipient As String
Dim strStatementMonth As String
Dim strThroughDate As String
Dim strHTML As String

Dim oItem As Outlook.MailItem

Set myItem = Application.CreateItemFromTemplate("C:\Users\username\AppData\Roaming\Microsoft\Templates\Statement.oft")
strHTML = myItem.HTMLBody
Set oItem = Session.ActiveExplorer.Selection.Item(1)
Set myItem = oItem.Forward

strLastName = InputBox("Recipients Last Name?")
strFirstName = InputBox("Recipients First Name?")
strPrefix = InputBox("Recipients Prefix (ex. Mr., Ms., Dr.)?")
strMatterID = InputBox("Matter Number?")
strStatementMonth = InputBox("What is the statement date?")
strThroughDate = InputBox("Services rendered through what date?")

myItem.HTMLBody = Replace(myItem.HTMLBody, "%STATEMENTMONTH%", strStatementMonth)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%THROUGHDATE%", strThroughDate)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%RECIPIENT%", strRecipient)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%PREFIX%", strPrefix)
myItem.HTMLBody = Replace(myItem.HTMLBody, "%LASTNAME%", strLastName)
myItem.Subject = Replace(myItem.Subject, "%LASTNAME%", strLastName)
myItem.Subject = Replace(myItem.Subject, "%FIRSTNAME%", strFirstName)
myItem.Subject = Replace(myItem.Subject, "%MATTERID%", strMatterID)
myItem.Display

End Sub

 

[Diane Poremsky]

Besides the typo in my last post (now fixed) - the top part of the macro is below. oItem is the original message, myitem is the forward.

Sub Forward_Statement2()
Dim myItem As Outlook.MailItem
Dim oItem As Outlook.MailItem
Dim strRecipient As String
Dim strStatementMonth As String
Dim strThroughDate As String
Dim strHTML As String

Set oItem = Application.ActiveExplorer.Selection.Item(1)
Set myItem = oItem.Forward
strHTML = oItem.HTMLBody