Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Emailing a template, Word VBA,  that includes attachments

Posted on 2014-02-07
11
Medium Priority
?
632 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 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