Solved

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

Posted on 2014-11-20
5
403 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 69

Expert Comment

by:Qlemo
ID: 40457208
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
ID: 40457769
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
ID: 40457881
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
ID: 40458061
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
ID: 40467180
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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