Solved: Multiple Email Attachments

[Shezageek]

I have a VBA project for Word 2003 that has a userform with multiple command buttons on it. Each command button opens another userform with multiple checkboxes. After you select one or more checkboxes, and click "OK" the document(s) will print. I would like to add the option to email the same documents that were selected as an attachment in one email. I tried the ActiveDocument.SendMail before the "End If" of each code for each checkbox. This would add that particular document as an attachment and launch Outlook, but I need it to add all of the documents that were selected.

Any help is appreciated.

S

[lucas]

Without seeing the code I would be guessing but you obviously have code that prints all of these documents, you would need to include the call to email them at the same point......same checkboxes, right?

[Shezageek]

Here is the code I have so far.


Const DIRECTORY_PATH As String = "C:\abcdisclosures\"
Private Sub CMD02Cancel_Click()
Unload Me
End Sub

Private Sub CMD02OK_Click()
ChangeFileOpenDirectory DIRECTORY_PATH
If Me.cbTCProdGuide = True Then
Documents.Open "01TCProductGuide.doc"
ActiveDocument.PrintOut
ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges
Selection.EndKey wdStory
Selection.TypeText "01TCProductGuide was printed"
Selection.TypeParagraph


End If
If Me.cbTCImplement = True Then
Documents.Open "02TCImplementationGuide.doc"
ActiveDocument.PrintOut
ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges
Selection.TypeText "02TCImplementationGuide was printed"
Selection.TypeParagraph


End If
If Me.cbTCFeeSchedule = True Then
Documents.Open "03TCFeeSchedule.doc"
ActiveDocument.PrintOut
ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges
Selection.TypeText "03TCFeeSchedule was printed"
Selection.TypeParagraph
End If
End Sub

Private Sub UserForm_initialize()
If ActiveDocument.Bookmarks.Exists("BKTCProductGuide01") Then
Me.cbTCProdGuide.Value = True
Else
Me.cbTCProdGuide.Value = False
End If

If ActiveDocument.Bookmarks.Exists("BKTCImplementationGuide02") Then
Me.cbTCImplement.Value = True
Else
Me.cbTCImplement.Value = False
End If

If ActiveDocument.Bookmarks.Exists("BKTCFeeSchedule03") Then
Me.cbTCFeeSchedule.Value = True
Else
Me.cbTCFeeSchedule.Value = False
End If
End Sub

Where and what code do I add?

Thanks
S

Edit Lucas: VBA Tags added to code

[lucas]

Well, unless you can post your document I would say that you could add another if statement at the end of your Private Sub CMD02OK_Click procedure to check to see which documents were printed or which checkboxes are checked and add them as attachments to your email.

Here is a good code to add the activedocument as an attachment to outlook. You will need to set a reference to the Outlook object library:

Sub eMailActiveDocument()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
With EmailItem
.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "User@Domain.Com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.FullName
.Display
' .Send
End With

Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End Sub

[Shezageek]

Will this allow me to add all three documents as attachments to one email? If you will tell me how to upload the file to you, I would be more than happy to do so. This is really frustrating me. Thank you so much for taking time to help me.

[lucas]

S, You can upload your file to a post by:

click on the post reply button at the lower left corner of the last post

when the page loads, add your message and then scroll down till you find a button that says "manage attachments" add your file there.

[Shezageek]

Here is the file. Please assist as you can. Thanks.

[lucas]

This works for me. You have to unload the userforms as you go to get this to work.....see button code in attachment. Get to the form to email by clicking on the Treasury Connect button, its the only one I looked at:


Public Sub CMDEmail_Click()
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)
Unload Me
With olMsg
.To = "X@z.com"
.CC = "y@z.com"
.BCC = "z@z.com"
.Subject = "subject"
.Body = "body"

If Me.cbTCProdGuide.Value = True Then
.Attachments.Add "c:\rbcdisclosures\01TCProductGuide.doc"
End If
If Me.cbTCImplement.Value = True Then
.Attachments.Add "C:\rbcdisclosures\02TCImplementationGuide.doc"
End If
If Me.cbTCFeeSchedule.Value = True Then
.Attachments.Add "C:\rbcdisclosures\03TCFeeSchedule.doc"
End If
.Display
End With

End Sub

[Shezageek]

OMG This is perfect. Thank you so much. I do have one other generic question though. At the top of that userform, the path to the documents is stated. Why is it, I have to put the entire path of the documents in this code in order to get it to work? I thought once the path was stated, that all things would point to that path.

Thank you so much for the code. This is great.

S :bow:

[lucas]

When you declare the path publicly you can use it in each procedure but you have to call it. In your print module it is called out like this:

ChangeFileOpenDirectory DIRECTORY_PATH


That sets the path for any file open operation in that routine only.

If you want to use it in the email you could change each path to this....change the filename for each:

.Attachments.Add DIRECTORY_PATH & "01TCProductGuide.doc"


The advantage of using it in each procedure is that if the path changes, you only have to change it in the code in one place.

Additionally if you want to keep the files with the document you are running the code from....in other words they are all in the same directory...you could use this for each one....change the filename for each.


.Attachments.Add ThisDocument.Path & "\01TCProductGuide.doc"


HTH

[lucas]

I would suggest that you add Option Explicit to the very top of every module, userform, etc. in the vbe. That way, If you forget to dim variables, etc. you will receive an error.......


Another note on using the forum, If you select your code when posting and then hit the vba button, it will be formatted as I have done to yours at post #3.

[Shezageek]

The file itself, will be stored as a template. The supporting documents are all in the same folder on the server. I tried changing the path to the DIRECTORY_PATH & and it didn't work. It just kept the command userform on the screen and did not launch Outlook. I'm not sure why????? Any suggestions? Thanks for your help. This has been great.

[lucas]

The file itself, will be stored as a template. The supporting documents are all in the same folder on the server.

Does that mean that the template is in the same directory as the 3 doc files? If so then change your if statments to this:

If Me.cbTCProdGuide.Value = True Then
.Attachments.Add ThisDocument.Path & "\01TCProductGuide.doc"
End If
If Me.cbTCImplement.Value = True Then
.Attachments.Add ThisDocument.Path & "\02TCImplementationGuide.doc"
End If
If Me.cbTCFeeSchedule.Value = True Then
.Attachments.Add ThisDocument.Path & "\03TCFeeSchedule.doc"
End If

[Shezageek]

I got the code to work as long as I am in VBA. When I run the template, I get an error message. See image.

[lucas]

It works fine for me. If you double click on the template......then you get the userform. Click on "Treasury Connect" and then select your templates and click on "email" It works fine.


The error indicates that you have a userform or messagebox still open. They have to be unloaded while the mail is sent......

[Shezageek]

I launched the template and had nothing else open. I still get this error message.

[Shezageek]

Here is the document in its entirety.

[lucas]

I fixed this for you in post #8 and explained what was happening. When you run the template is not the userform01 still showing in the background? That is the problem.

replace this code in userform01:


Private Sub cmdTC_Click()
UserForm02TC.Show
End Sub


with this:


Private Sub cmdTC_Click()
Unload Me
UserForm02TC.Show
End Sub


Please re read post #8 so you will understand why this is happening. You must unload all userforms before you can send the mail.....

[Shezageek]

Thanks. . . that fixed it.

[lucas]

I could have fixed it for you but you wouldn't have understood the problem and it would have happened again......

Glad you got it sorted. Be sure to mark your thread solved using the thread tools at the top of the page.