?
Solved

Emailing a template, Word VBA,  that includes attachments

Posted on 2014-02-07
11
Medium Priority
?
625 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 40

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 40

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 40

Expert Comment

by:als315
ID: 39851215
Are you running my code from Outlook? Can you show errors?
0
 

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 40

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 40

Accepted Solution

by:
als315 earned 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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