Emailing a template, Word VBA, that includes attachments

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
Cook09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
Cook09Author Commented:
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
als315Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Cook09Author Commented:
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
als315Commented:
Are you running my code from Outlook? Can you show errors?
0
Cook09Author Commented:
I'll try and have some feedback tomorrow.  Customer priorities tend to overshadow everything else.  Thanks for your patience.

Cook
0
Cook09Author Commented:
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
als315Commented:
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
Cook09Author Commented:
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
als315Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cook09Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.