Automated Mail Merge and Email

[roxyjo11]

I have a code that automates a mail merge based on the current record (works great) and generates an email. The email part is giving me problems. What I would love to do is be able to generate an email based on a text file, and attach the mail merged document. I may be wanting too much, but if I could get help with the email based on the text file, i'd be ecstatic!!! The email works, but I can't get it to feed from the file. I'm new to VBA, and everything I've found has been in a completely different format! I have Access 2003, and all the other Microsoft programs are 2007.

Thanks for any help in advance!

Code:
Private Sub Command37_Click()
Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String
Set oMainDoc = oApp.Documents.Open("z:\HIPAA\Breach Notification\Template Letter")
oApp.Visible = True

With oMainDoc.MailMerge
.MainDocumentType = wdFormLetters
sDBPath = "z:\HIPAA\Breach Notification\Breach Notification.mdb"
strCriteria = [Forms]![All Breach Records]![New ID]
.OpenDataSource Name:=sDBPath, _
SQLStatement:="SELECT * FROM [Spreadsheet Breach Info Query]" & _
"WHERE [Spreadsheet Breach Info Query].[New ID] IN(" & strCriteria & ");"



End With
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute
End With

oApp.Activate
oApp.Documents.Parent.Visible = True
oApp.Documents(2).Close wdDoNotSaveChanges
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1

On Error GoTo err_Command37_click
Dim strRecipient As String
Dim strRecipient2 As String
Dim strSubject As String
Dim strMessage As String
strRecipient = "0" & Me.Store_Number & "-US-RX MANAGER"
strRecipient2 = Me.Market_Manager_Name & " " & Me.market_manager_last_name
strSubject = "HIPAA Breach Notification"
strMessage = "Test" & vbCrLf & vbCrLf
DoCmd.SendObject acSendNoObject, , , strRecipient, strRecipient2, , strSubject, strMessage
exit_Command37_click:
Exit Sub
err_Command37_click:
If Err.Number = 2501 Then
MsgBox "Email was canceled!", vbInformation
Else
MsgBox Err.Number & "; " & Err.Description
End If
Resume exit_Command37_click
End Sub

 

[darbid]

I am a little confused from your code what you want to do. Your code just looks like the mailmerge part.

So now you would like to send an email through Outlook?
Use the mailmerge document as an attachment for this email?
Add some text to the body of this email which is stored in a text file?


Are you doing this from a MDB or from what?
Can you show your code to send an email?
Why is the body of your email contained in a text file? Reading a text file can be done but is probably not what should be done in 2009. (peoples opinions may vary and I still use a text file as an ini file for my MDB)

 

[roxyjo11]

Mail Merge piece:
Dim strRecipient As String
Dim strRecipient2 As String
Dim strSubject As String
Dim strMessage As String
strRecipient = "0" & Me.Store_Number & "-US-RX MANAGER"
strRecipient2 = Me.Market_Manager_Name & " " & Me.market_manager_last_name
strSubject = "HIPAA Breach Notification"
strMessage = "Test" & vbCrLf & vbCrLf
DoCmd.SendObject acSendNoObject, , , strRecipient, strRecipient2, , strSubject, strMessage
exit_Command37_click:
Exit Sub
err_Command37_click:
If Err.Number = 2501 Then
MsgBox "Email was canceled!", vbInformation
Else
MsgBox Err.Number & "; " & Err.Description
End If
Resume exit_Command37_click
End Sub

The code works great right now, doing what it is written to do now. I just need a couple little changes, that I'm not sure of how to do.

This is all being done from a Database. Yes, I have a document that needs to be merged. And it would be great to have that document attached to the email, but if it's not possible, I can manually do that part. Right now I have it set up where I can just type what I want in the email. I'm not sure how to have the email actually formatted like an email. From what I can tell from other threads, it is possible to do with a text file.

Thanks for your help!

 

[darbid]

Oh sorry I did not see the SendObject there. I am not sure that you can send attachments other than Access Objects with this method. Maybe someone else will correct me on this.

If you feel like doing this a little differently I would suggest you use Outloook like this example. You can then add your email to the body of the email and you can add as many attachments as you like.

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem

On Error GoTo ErrorHandler

Set oApp = GetObject(, "Outlook.Application")

    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
       .To = address
       .Attachments.Add "c:\a.txt" 
       .Attachments.Add "c:\abc.txt"
       .Subject = "SubjectSomthing"
       .Body = str
       .Display
    End With

If bln_QuitOutlook Then
    oApp.Quit
End If


    oEmail = Nothing
    oApp = Nothing


ErrorHandlerExit:
   Exit sub

ErrorHandler:
   
   Select Case Err
   
    Case 429
    'Outlook is not running; open Outlook with CreateObject
      Set oApp = New Outlook.Application
      bln_QuitOutlook = True  'set flag to true so that we quit outlook at the end
      Resume Next
   
   Case Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit

 

[roxyjo11]

Thanks so much for your help.

I seem to still be having issues! When I make the str be strtemplate, and make that equal to a path to a file. It doesn't work, it just inserts the path as text. I am probably doing something wrong, making it much more difficult that necessary.
Also I can't seem to add this to my mail merge code to have it all happen with one click. Once again I'm sure there's a better way to do it!!

Also is there a way to make the mail merged document automatically save?

Once again thank you!!!

 

[darbid]

show me you code so far for the email section.

We will deal with how to put this all together later by using a function.

 

[roxyjo11]

Email code:

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
On Error GoTo ErrorHandler
StrTemplate = "c:\Pharm Manager.rtf"
Set oApp = GetObject(, "Outlook.Application")
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
.To = "Address"
.CC = "Address"
.Attachments.Add "c:\Roxy Test.doc"
.Subject = "SubjectSomthing"
.Body = StrTemplate
.Display
End With
If bln_QuitOutlook Then
oApp.Quit
End If

oEmail = Nothing
oApp = Nothing

ErrorHandlerExit:
Exit Sub
ErrorHandler:

Select Case Err

Case 429
'Outlook is not running; open Outlook with CreateObject
Set oApp = New Outlook.Application
bln_QuitOutlook = True 'set flag to true so that we quit outlook at the end
Resume Next

Case Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Select
End Sub

 

[darbid]

I seem to still be having issues! When I make the str be strtemplate, and make that equal to a path to a file. It doesn't work, it just inserts the path as text. I am probably doing something wrong, making it much more difficult that necessary.

strtemplate is your text file for the body of the email or is it the attachment? Your code looks like it is to be the text for the body of your email. What you have below will not work. Outlook cannot read that text we will have to have another way of doing that.

Also I can't seem to add this to my mail merge code to have it all happen with one click. Once again I'm sure there's a better way to do it!!

Also is there a way to make the mail merged document automatically save?

Once again thank you!!!

What we are going to do is set up a SUB for your email sending and call this from the mailmerge SUB.

I am not going to do it for you but set you up

just underneath your meailmerg SUB make a new sub like this you are going to have to add ALL of the variables to this that you need. I have added the first two to show you how.

[/code]
Private Sub sendmailmerge(StrTemplate as string, strTo as string, .........)



End Sub

Then in your mail merge where you used sendobject you are going to use

Call sendmailmerge(StrTemplate, gogo@gmx.com, ..........)