Solved

Forward email attachment via Outlook 2010 vba macro and template with userform instead of inputbox

Posted on 2014-11-20
5
380 Views
Last Modified: 2014-11-26
I have the following Outlook vba macro that works good, but I want to make it better.  I select an email with attachment in my inbox and activate the macro.  The macro prompts the user for input like First Name, then another prompt for Last Name, then another prompt for Statement Date, etc., etc.  It then calls an Outlook template and replaces text within the html body and subject of the template with the values entered at the inputbox prompts.  It also adds the attachment from the original email (in my case a pdf) along with some static text in the template.  

The down side is that the user gets several input boxes one after the other which is kind of clunky.  Also, if they accidentally triggered the macro they have to keep clicking OK on one input box after another to kill the macro instead of just being able to click cancel once to stop the prompts.

Is it possible to use a userform so that the end user just gets one prompt asking them to enter all the variables in one pop up where they could fill in all the fields and click okay just once or if appropriate click a cancel button to stop the macro from running any further?      

Here is the macro as it exist:

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

Dim fs As Object
Dim Atmt As Attachment
Dim FileName As String

Dim Inbox As MAPIFolder
Dim MyItems As Items
Dim objOutlookAttach As Outlook.Attachment

Set Inbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set MyItems = Inbox.Items
Set myToBeForwarded = Application.ActiveExplorer.Selection(1)
Set fs = CreateObject("Scripting.FileSystemObject")

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)
 
For Each Atmt In myToBeForwarded.Attachments
    'save it in C:\temp
    FileName = "C:\TempPDF\" & Atmt.FileName
    Atmt.SaveAsFile FileName
    'now attach it to the new message
    Set objOutlookAttach = myItem.Attachments.Add(FileName)
    fs.deletefile FileName, True
Next
 
myItem.Display

End Sub
0
Comment
Question by:HogRing
  • 2
  • 2
5 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
It is not difficult to create and use a form in Outlook for that. It is difficult to deploy it to other Outlook clients, though. Unless we use the Windows.Forms library to dynamically create the form, which is kind of tedious ...
0
 

Author Comment

by:HogRing
Comment Utility
I only have a couple users to deploy it to so I can do it manually.  I don't know if  it's possible to use a form though or how to modify my macro to use it.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
I am not a guy who has messed with UserForms.
That stuff is certainly doable.
As @Qlemo has noted, you have to deploy it -- but then the macro modules had to be deployed anyway, so that isn't a showstopper.

Some things are more easily remedied though.
When you cancel an InputBox, or leave it blank, it returns "".
You can test for that, and exit the macro if it occurs.

Instead of Just
strLastName = InputBox("Recipients Last Name?")
you'd have
strLastName = InputBox("Recipients Last Name?")
if StrLastName = "" then exit sub


You'd set that up for all your InputBoxes, and you'd be able to bail on the macro at any point by not entering data
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Attached is a form
StatementForm.frm
that I exported from Outlook
StatementForm.frx was created at the same time
I've attached both.

This is the code that will open the form once you import it
Public Sub OpenTheForm()
StatementForm.Show
End Sub

Open in new window


This is the code behind the form
Option Explicit
Private Sub cmdSend_Click()
Dim myItem As Outlook.MailItem
Dim myToBeForwarded As Outlook.MailItem
Dim strRecipient As String
Dim strLastName As String
Dim strFirstName As String
Dim strPrefix As String
Dim strMatterID As String
Dim strStatementMonth As String
Dim strThroughDate As String
Dim strHTML As String

Dim fs As Object
Dim Atmt As Attachment
Dim FileName As String

Dim Inbox As MAPIFolder
Dim MyItems As Items
Dim objOutlookAttach As Outlook.Attachment

Set Inbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set MyItems = Inbox.Items
Set myToBeForwarded = Application.ActiveExplorer.Selection(1)
Set fs = CreateObject("Scripting.FileSystemObject")

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

strLastName = Me.txtLastName
strFirstName = Me.txtFirstName
strPrefix = Me.txtSalutation
strMatterID = Me.txtMatterNo
strStatementMonth = Me.txtStatementMonth
strThroughDate = Me.txtThroughDate



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)
 
For Each Atmt In myToBeForwarded.Attachments
    'save it in C:\temp
    FileName = "C:\TempPDF\" & Atmt.FileName
    Atmt.SaveAsFile FileName
    'now attach it to the new message
    Set objOutlookAttach = myItem.Attachments.Add(FileName)
    fs.deletefile FileName, True
Next
 
StatementForm.Hide
myItem.Display

End Sub

Open in new window


I think that should do it.
I didn't put any kind of error handling in
(check for dates, blank strings, valid data or anything else)
StatementForm.frm
StatementForm.frx
0
 

Author Comment

by:HogRing
Comment Utility
Thanks Nick67.  I ended up using your "exit sub" suggestion but I'm going to keep the form data and see if I can get that to work for another project.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Use email signature images to promote corporate certifications and industry awards.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now