merge multiple documents in ONE email

[Ploemen]

Hi,

I have a small problem with which I need some help. I want to use a macro to convert two access-reports to .rtf and paste them in ONE email.

Now I have used a macro with the step 'send object' in which I define the options to convert to rtf and the persons to send the email. When I use two of those steps in this one macro Acess sends two emails.

What do I have to do (preferably in a simple macro) to merge to access-reports in one email??

Thanks for your help,

Maarten

 

[Blorf]

I don't beleive you can do this with a simple macro, but the vba code is not much.

You will need to export your reports, then create an e-mail, and attach each report.

The code you need I found here at tek-tips, looks like

Set ObjOutlook = CreateObject("Outlook.Application")
Set ObjEmail = ObjOutlook.CreateItem(olMailItem)

With ObjEmail
.To = M_Address
.ReadReceiptRequested = False
.body = "The attached outlines Projects Steps assigned for completion." & Chr(10) & Chr(13)
.Subject = "Projects Step Assignments"
Set objOutlookAttach = .Attachments.Add("K:\Charles\Project_Assignment.RTF")
.Send
End With

ChaZ

 

[Ploemen]

Hi ChaZ,

Thanks for your answer but it just isn´t working yet.
My knowledge about VBA is zero so i think i need a complete code. the problems start in the following sentence Set ObjOutlook = CreateObject("Outlook.Application")

Is there something i have to adjust here?

Thanks a lot!

 

[Blorf]

Well, I assumed that you are using outlook to send/receive e-mail. Is that correct?

ChaZ

 

[PaulBricker]

It sounds like you have a reference problem. Open up a VBA module and on the Menu bar go to Tools...References. Look for the Outlook 9.0 library and make sure there is a check in the box to the left.
Then to send multiple attachments look at this post

http://www.tek-tips.com/viewthread.cfm?qid=968653

Post back if you have specific questions.

Paul

 

[Ploemen]

Indeed I am using outlook to send email.
And I have checked that there was a check in the box of the Outlook 9.0 Library (it is possible to send email via a macro so that can't be the probem, i guess).

The problem is still there, unfortunately.

Using the code Paul suggested the error is in this sentence

Dim myOLItem As Outlook.MailItem

the error text is: Compilation error - A by the user defined type of data is not defined.

Hope this helps you to help me

thanks again

 

[PaulBricker]

It might still be a references issue if you don't have all the correct libraries available. Can you post the references that are checked in your list so I can compare them with mine. Also, at the top of your VBA module, do you have

Option Compare Database
Option Explicit

showing. If you do, you can get rid of the Option Explicit and see if that helps.

Paul

 

[Ploemen]

I don't know how but i have succeeded. However a new problem has arisen. This is the vba code i use:

Private Sub Knop43_Click()
On Error GoTo Err_Knop43_Click

Dim stDocName As String

stDocName = "Macro3"
DoCmd.RunMacro stDocName

Exit_Knop43_Click:
Exit Sub

Err_Knop43_Click:
MsgBox Err.Description
Resume Exit_Knop43_Click

End Sub

Sub SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Maak de Outlook-sessie.
Set objOutlook = CreateObject("Outlook.Application")

' Maak het bericht.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Voeg de geadresseerden voor het vak Aan toe aan het bericht.
Set objOutlookRecip = .Recipients.Add("maarten.ploemen@obvion.nl")
objOutlookRecip.Type = olTo

' Voeg de geadresseerden voor het vak CC toe aan het bericht.
Set objOutlookRecip = .Recipients.Add("mploemen@hotmail.com")
objOutlookRecip.Type = olCC

' Stel het onderwerp, de berichttekst en de urgentie voor het bericht in.
.Subject = "Dit is een test van Automatisering met Microsoft Outlook"
.Body = "Dit is echt de laatste test." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'Hoge urgentie

' Voeg bijlagen toe aan het bericht.
If Not IsMissing("K:\l-Obvion\Concurrentieanalyse\Attachments\RenteBASIS.snp") Then
Set objOutlookAttach = .Attachments.Add("K:\l-Obvion\Concurrentieanalyse\Attachments\RenteBASIS.snp")
Set objOutlookAttach = .Attachments.Add("K:\l-Obvion\Concurrentieanalyse\Attachments\Rente125%.snp")
End If

' Zet de naam van elke geadresseerde om.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
End Sub

The macro 3 is the one I use to start the vba code. Execute procedure and then SendMessage.

The only problem know is that I receive the email three times. Wat can be the cause of this???

 

[PaulBricker]

I don't see any errors. I ran your code just changing the recipients and the documents and I only got one copy. Also, I remmed out your lines

If IsMissing
and
End If

because I didn't want to deal with them, but that shouldn't create a loop that sends the mail three time. Does Macro3 do anything besides run the email? You could just call the sub procedure from the click event and bypass the macro altogether and see if that helps.

Paul

 

[Ploemen]

It is unbelievable, but if I run the VBA code in the VBA programme I receive one email.

And if I run it in a form or with a macro I receive three emails. I am doing nothing special, just making the command SendMessage

 

[PaulBricker]

You might try putting a break early in the code so that you can step thru it when calling it from your Form and see if you can detect what's going on. There must be something forcing addtional mailings and the only way I would know to find it would be to step thru the code one line at a time.
When I get some time, I will try running it from a form and see what I find.

Sorry I can't be more helpful

Paul

 

[PaulBricker]

I tried to duplicate you problem but with no success. You might want to post your question here

forum605

It is an E-Mail issues forum.
Hope this helps.

Paul