Multiple attachments on E-mails? 11

[Peps]

I’m using the following code to send an E-mail with a query attached:

DoCmd.SendObject acSendQuery, "Name of attachment being saved", acFormatXLS, [Name of my query being attached], , , "Text message", , False

Can anyone tell me how I can attach two queries on the same E-mail?

Many thanks guys.
Peps

 

[Quehay]

You know this is something that's really lacking in basic Access interactive functionality. To set it up programmatically would be a pain, as far as I know the best way would be to save query output to file and then start outlook and grab the files.

It's not that hard to automate Outlook and generate a new mail with attachment. The part that's missing is having an object variable that could hold the output of the docmd. and then reference that object in the mail creation routine.

The easy way: Save output to Excel. Start mail editor and attach the files.

 

[Smack]

Peps, I don't know how to write code, but have you tried using SendObject with multiple actions in a macro? it worked for me.
Smack

 

[PaulMilnes]

I agree with Smack - it's not much bother to duplicate the line of code for a second or third attachgment. I have a database with a button that sends details of students via e-mail to a colleague. It has to send three files, so I just use SendObject three times.

If you don't know at coding time how many queries you're sending, but if not, you could always send them in a loop (providing you can determine how many there are!)

Paul Milnes

 

[jelwood01]

I have found that using an instance of Outlook is cleaner and easier than the DoCmd method. If you have varying numbers of attachments you can create a text box to enter in the file name and location, a button to add the information to a list box, then a button that will loop through the info in the list box building a string with the .Attachments.Add statement and drop that into the With statement.

Hope this helps...

jelwood01

Private Sub cmdSendAtt_Click()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = "Buddy@Attachment.com"
.Subject = "Hey..."
.HTMLBody = &quot;Check It Out<br><br>&quot;
.Attachments.Add &quot;C:\Stuff.doc&quot;, olByValue, 1, &quot;Stuff&quot;
.Attachments.Add &quot;C:\Thing.doc&quot;, olByValue, 1, &quot;Thing&quot;
.Send
End With
End Sub

 

[MisterC]

Make sure you have a reference to &quot;Microsoft Outlook 9.0 Object Library&quot; or equivalent or you will get a &quot;...user defined type&quot; error.

 

[Peps]

Many thanks guys,

Although Smack was probably right (up to a point) Jelwood's code is gona help a real bunch. One of the reasons I wasn’t to keen on using a macro for sending objects, is that you cannot use a variable for the first time receiver or person in cc: (or at least I don’t think so!) Either way I'll keep it clean by modifying Jelwoods's code.

Once Again...
Thanks Guys
Peps

 

[Smack]

Limited is the key word! I have got to take a visual basic class. Trying to do everything without knowing SQL or VBA makes life (in the politically correct term) challenging!
Smack

 

[jelwood01]

I have taken a couple of crash courses in VB and SQL and found that the best way to &quot;figure it all out&quot; is to just experiment. Here are the four books that I use as references:

1. Platinum Edition: Using Access 97; Jennings; QUE
2. Access 97 Developer's Handbook; Litwin, Getz, Gilbert; Sybex
3. Access 97 Macro & VBA Handbook; Novalis; Sybex
4. VB & VBA In A Nutshell; Lomax; O'Reilly

Good luck and have fun!

jelwood01

 

[LonnieJohnson]

Nice move jelwood01. I picked up a link to this thread in a more recent inquiry about the above.


ljprodev@yahoo.com
ProDev, MS Access Applications

http://www.galaxymall.com/software/PRODEV

 

[jahlmer]

My God, you genius! I've spent months searching for something like this! This ROCKS!

 

[NebraskaGod]

Make sure you have a reference to &quot;Microsoft Outlook 9.0 Object Library&quot; or equivalent or you will get a &quot;...user defined type&quot; error.

Where do I add this?

 

[jelwood01]

when you are in a module go to Tool-->References and select Microsoft Outlook 9.0.

jelwood01

 

[NebraskaGod]

This is Great! I don't want to send attachments, I want to put this at the end of a form and have it serve two functions: Write the record to the table and send the current record in an email. Any help is greatly appreciated.

 

[rccline]

I have been struggling with SQL and ADO and can't seem to get anywhere! (Access2000)

Rather than relate to a specific email address, how do I use the code above tocreate a variable that relates back to a field in a query?

I need to send separate emails for each email address returned by the query, not a batch send.

With MailOutLook
.To = &quot;Buddy@Attachment.com&quot;
.Subject = &quot;Hey...&quot;
.HTMLBody = &quot;Check It Out<br><br>&quot;
.Attachments.Add &quot;C:\Stuff.doc&quot;, olByValue, 1, &quot;Stuff&quot;
.Attachments.Add &quot;C:\Thing.doc&quot;, olByValue, 1, &quot;Thing&quot;
.Send
End With
End Sub


Thank you!

Robert

 

[LonnieJohnson]

Use this code. MyField is the field in your query that has the email adresses to mail to.


Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;SELECT MyField FROM MyQuery&quot

Do Until rs.EOF

With MailOutLook
.To = rs!MyField
.Subject = &quot;Hey...&quot;
.HTMLBody = &quot;Check It Out<br><br>&quot;
.Attachments.Add &quot;C:\Stuff.doc&quot;, olByValue, , &quot;Stuff&quot;
.Send
End With

End Sub

rs.MoveNext
Loop

Hope this helps.



prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[RobPotts]

Hi Jelwood01

I've just been looking at your code and I don't understand what to do with it. What I want it to do is create an email with a report on it called &quot;releases&quot;. However want the e-mail to have a snapshot version and a RFT version of this on the same e-mail. Is this possible with your code and if so can you talk me through what I need to do step by step.

Thanks in advance
Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[jelwood01]

Rob,

Depending how you want to display the info there are a couple of options availble. First is to pack the report info into the body of the email and send it sans attachments. The other option, which I have used for more complicated documents, is to instantiate Word and write the data and formating to that. I save the attachment in a standard location with a file name based on a compound key, then I pick up the attachment and mail it. Depending how you are doing the attachments this can be pretty easy.

I can send along some code examples to your email if you would like. If people are interested I can post them up here, they are lengthy.

jelwood01

jelwood01@hotmail.com

 

[RobPotts]

Could you please post the code to me at
Robeetpotts1@hotmail.com

Cheers Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[RobPotts]

Jelwood01,

A couple of things that you said above have clicked into place, and now I've managed to sort it. I worked ou how to use the above code and it works fine in access 2K. The only problem (hopefully soon to be rectified), is that I can't save snp files in access 97 using the outputTo code. But I'm trying to get everybody on2K now so I should be OK. The code I now have looks like this.

DoCmd.OutputTo ObjectType:=acOutputReport, _
   objectname:="XXX", _
   OutputFormat:=acFormatSNP, _
   OutputFile:="C:\Temp\XXX.snp" ', _
   Autostart:=True
   
   DoCmd.OutputTo ObjectType:=acOutputReport, _
   objectname:="XXX", _
   OutputFormat:=acFormatRTF, _
   OutputFile:="C:\Temp\XXX.RTF" ', _
   Autostart:=True
   
   Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

        With MailOutLook
            .To = "ME"
            .Subject = "Batch Releases"
            .HTMLBody = "Hello"
            .Attachments.Add "C:\Temp\XXX.snp", olByValue, 1, "XXX_SNP"
            .Attachments.Add "C:\Temp\XXX.RTF", olByValue, 1, "XXX_RTF"
            .Send
        End With[Code]

Thanks for your help have a star Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit 
  and wisdom to do their job properly.