Solved

Emailing a template, Word VBA,  that includes attachments

Posted on 2014-02-07
11
604 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
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…

803 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