Solved

Emailing a template, Word VBA,  that includes attachments

Posted on 2014-02-07
11
595 Views
Last Modified: 2014-03-12
After constructing a Report within MS Word, what would be the best process for having a macro that opens an Outlook template and attaches the current document, plus one other, and then Sends the e-mail out to those designated within the Outlook template.  

Or, maybe the better question is what is the best process for using VBA, either in Word or Outlook, to open the template, and attach two files that have the current days date within the filename, and then sends it out.  Currently, the two Word files are saved as, with each one having a different path or subdirectory, as:

           vPath & "\" & Format(Date, "mm.dd.yy") & " Error_Log_Report.doc(x)
           vPath & "\" & Format(Date, "mm.dd.yy") & " EI_Log_Report.doc(x)

Cook09
0
Comment
Question by:Cook09
  • 6
  • 5
11 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39843967
You can use something like this mmacro in Outlook:
Sub mailfiles()
Dim Fn As String
Dim VPath As String
Dim myMail As Outlook.MailItem
VPath = "c:\tmp" 'Correct path to your files
    Set myMail = Application.CreateItemFromTemplate("C:\tmp\report.oft") 'Set correct path to message template
    myMail.To = "Email address" 'Set proper email address
    Fn = VPath & "\" & Format(Date, "mm.dd.yy") & " Error_Log_Report.doc" 'Select correct extension - doc or docx
    myMail.Attachments.Add Fn
    Fn = VPath & "\" & Format(Date, "mm.dd.yy") & " EI_Log_Report.doc"  'Select correct extension - doc or docx
    myMail.Attachments.Add Fn
    myMail.Display
    Set myMail = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Cook09
ID: 39848369
als315

 Is there a way to pull the vPath from Excel 2010?  In a particular application, I open a file from where the attachments will be located.

 
vPath = Workbooks("_Error Report Automation.xlsm").Path

Open in new window


Cook09
0
 
LVL 39

Expert Comment

by:als315
ID: 39849198
Yes, you can set it as you like. If it is Active Workbook (from Excel), you can use:
vPath = Application.ActiveWorkbook.Path
If this code will be started from Outlook, you should know path to your files.
0
 

Author Comment

by:Cook09
ID: 39850989
Sorry I haven't gotten back with you, I'm having a difficult time with our network and/or permissions to call / open the template.  I may not be openning it correctly, but where ever I place it, it errors out.  I'm almost at the point of placing it in a common location, and let them easily select it from the "Choose Form" button on the QAT.  It's frustrating at the moment.

I have read where someone used the:

.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
.Attachments.Add Source:=ActiveDocument.TextBoxFile1.Value, Type:=olByValue

Open in new window

approach, but without any other code that ties it all together.  One other item, it was my mistake in mentioning Excel and not Word.  I have used Excel quite a bit, and the underlying data is in Excel, but the generated report(s) is actually in Word.

Cook09
0
 
LVL 39

Expert Comment

by:als315
ID: 39851215
Are you running my code from Outlook? Can you show errors?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Cook09
ID: 39857847
I'll try and have some feedback tomorrow.  Customer priorities tend to overshadow everything else.  Thanks for your patience.

Cook
0
 

Author Comment

by:Cook09
ID: 39883776
als315,

Sorry it has taken so long, but work demands didn't allow me to concentrate on this until now.

I was thinking about using Outlook to open the template and then attach whatever Word document that was needed.

I went  through the code and got most of it to work. The one issue that I could have is the path that would be common to most users.  In Excel VBA, just by opening up another file, one can capture the path, regardless of how it may be mapped on someone's computer.

If I were to put the vba code into Microsoft Word and call it from the ActiveDocument, and attach the ActiveDocument to the Outlook Template, how would that code look like.

Given that this is new to everyone here, all I really have to have is:
Sub mailfiles()
Dim Fn As String
Dim VPath As String
Dim myMail As Outlook.MailItem
     Set myMail = Application.CreateItemFromTemplate("C:\temp\Error Log.oft")
     myMail.Display
     Set myMail = Nothing
End Sub

Open in new window

The Mail.To and CC: are part of the template.
They could then attach the needed documents manually.

 But, I would like to attach the Active Word Document.  The problem is the path may be different for a couple of users.  What would be the best way to accomplish this.  I've tried to use
vPath = Documents(ActiveDocument.FullName).Path
But I get an error that the "Remote Server machine is unavailable or out of service."

 However, I can use Excel, open up an application, and get its path.
Cook
0
 
LVL 39

Expert Comment

by:als315
ID: 39884700
You will have problems if your Active document will contain macros: type should be docm, your users will have warning if path is not in trusted. And the last - it is not very good idea to mail documents with macro, because in many cases it is not allowed by mail clients a¿¿ servers. You can create some document with autorun macro, which will be started from Vpath, create message, attach necessary files and closed.
In Excel:
VPath = ActiveWorkbook.Path
Word:
Vpath = ActiveDocument.Path
0
 

Author Comment

by:Cook09
ID: 39885717
Question though:  If I open a Word document in its own window, in the VBE window, under Normal, it lists a number of macros that I may use from time to time.  Yet I can still save as a .doc or .docx.  Does Microsoft strip out "Normal" if saved in a non-.docm format?  Or, do I need to create something that doesn't use Normal to store some of the more commonly used macros?

Cook
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39886580
Normal is normal.dot template, where your local macros are stored usually. They will not be available on another computer. Read this article about macros distribution:
http://word.mvps.org/faqs/macrosvba/DistributeMacros.htm
0
 

Author Closing Comment

by:Cook09
ID: 39923748
With  both the "Active Document Path" and pointing to the Normal.dot article, it effectively provided the information needed to accomplish what was needed.  Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Email signatures have numerous marketing benefits. Here are 8 top reasons to turn your email signature into a marketing channel.
Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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: …

706 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

18 Experts available now in Live!

Get 1:1 Help Now