Hi All, I am fairly new to VBA. I have recently been working on a Database program and at one point, I execute a private sub to send a snapshot of a report. I also want to include a full .xls into the email. I can't seem to figure this one out.

I have the Beginning Access 2000 by Wrox, Chapter 9 shows how to include the snapshot in the email but not how to also include any other files.

I can set the edit message to true and insert it myself, but I would like some automagic fix.

Any help would be greatly appreciated

What is the means of sending an email with a snapshot in it? (Not having this book, it would help to know what it is that you are doing.)

To answer the question: I am writting an application that prints a Ticket for an Offence under the OHSA. The Application will also print a Summons under the OHSA. The Courts and our Crown Prosecutor need to receive a snapshot of the ticket only but need a snapshot of the Summons and an .xls document sent as well.

[This is the SUB Code for a Ticket….ALL OK]

'This is where the EMAIL Happens
Dim SSU As String 'For SUBJECT LINE
SSU = (MB20) & ", " & (MB21) ' SSU is the Subject Line for the email
Dim SLSBA1 As Variant 'For SEND TO LINE
Dim SLSBA2 As Variant 'For CC LINE
Dim SLSBA3 As Variant 'Program Assistants Email Address
DoCmd.OpenTable ("tblINSID"), acViewNormal, acEdit
Dim recSTO As Recordset
Set db = CurrentDb()
Set recSTO = db.OpenRecordset("tblINSID")
recSTO.MoveFirst
SLSBA1 = recSTO.Fields("OutMail1") 'SLSBA1 is the SEND TO LINE for Email
SLSBA2 = recSTO.Fields("IDEmail") 'SLSBA2 is the CC LINE for Email
SLSBA3 = recSTO.Fields("OutMail3") 'SLSBA3 is the PA's Address for Email
DoCmd.RunMacro ("F35Mac")
Dim TTS1 As String 'Messge to appear in Body of email for Ticket sent to LSB
TTS1 = "This email was Auto-Generated by the PITP Version 3.0 Ticket Program"
'DoCmd.SendObject acSendReport, "LSBTicket", acFormatSNP, SLSBA1, SLSBA2, SLSBA3, SSU, TTS1, False
'SNP copy of the current ticket was sent.

[This is the SUB Code for A Summons…..Need Snapshot and .xls]

'This is where the EMAIL Happens
Dim SSU As String 'For SUBJECT LINE
SSU = (MB20) & ", " & (MB21) ' SSU is the Subject Line for the email
Dim SLSBA1 As Variant 'For SEND TO LINE
Dim SLSBA2 As Variant 'For CC LINE
Dim SLSBA3 As Variant 'Program Assistants Email Address
DoCmd.OpenTable ("tblINSID"), acViewNormal, acEdit
Dim recSTO As Recordset
Set db = CurrentDb()
Set recSTO = db.OpenRecordset("tblINSID")
recSTO.MoveFirst
SLSBA1 = recSTO.Fields("OutMail2") 'SLSBA1 is the SEND TO LINE for Email(Summons)
SLSBA2 = recSTO.Fields("IDEmail") 'SLSBA2 is the CC LINE for Email (Inspector)
SLSBA3 = recSTO.Fields("OutMail3") 'SLSBA3 is the PA's Address for Email
DoCmd.RunMacro ("F35Mac")
Dim TTS1 As String 'Message to appear in Body of email for Summons sent to LSB
Dim INTX As String 'addition Message
TTS1 = "This email was Auto-Generated by the PITP Version 3.0 Ticket/Summons Program. "
INTX = "Included in this email is a snapshot of the Summons and an Auto-Populated PNR"
DoCmd.SendObject acSendReport, "rptPNRCopy", acFormatSNP, SLSBA1, SLSBA2, SLSBA3, SSU, TTS1 & INTX, True
'Location is C:\MOLApps\PITP\Version3.0
'PNR is PITP-PNR.xls
'Copy of Summons is PITP-Summons.snp
MsgBox ("Insert File....C:\MOLApps\PITP\Version3.0\PITP-PNR.xls")
'Email of Summons Completed

The end of the DoCmd for the ticket is False, this will send the email Automatically. The ending is set to True for the Summons SUB so the user can manually insert the .xls

I am hopin to have a way to insert the .xls automatically and I can change the True to False. We will have several users if the application works and not all users are comfortable with computers, the easier I make it the better.

This application was created by myself with very limited knowledge of Access or VBA (I had to buy the book to have a clue). I hope that I can get this part of the application working, the rest works perfect.

Hope this information helps....

You really should make strings type String, rather than Variant. It will guard your code from some kinds of errors that otherwise are hard to find, makes your code more self-documenting, and strings in places where strings belong run faster than Variants in places where strings belong.

Also, code is a lot easier to read if you gather declarations together, and separate logical blocks with a blank line. So     Check out this link for a more robust way to send email that will give you the functionality that you need.

http://www.mvps.org/access/modules/mdl0019.htm

Looking into this (just a little bit, so this is not definitive) leads me to think that DoCmd won’t be able to do what you want.

Thanks Brian, I really appreciate your effort and help in this matter.
I tried the Access Module but cannot get it to work as "User Defined" errors come up. I have set the CDO in the Reference but still no luck. However I did play with the GetObject Function

Set PNR = GetObject ("C:\MOLApps\PITP\Version3.0\PITP-PNR.xls")

In the Local Window this appears to be OK.

However When I try to send via email I get an error stating that the type of file is invalid.

DoCmd.SendObject acSendObject, PNR, Excel.Application, SLSBA1, SLSBA2, SLSBA3, SSU, TTS1 & INTX, True

I also cannot see the the spreadsheet when I try:
PNR.Visible=True
All I see is a blank xls, no sheets.

I feel I am getting closer to the solution of inserting this xls as I can see the structure in the local window and if I can figure out the type of file error.

I guess I will have to concentrate on getting the Outlook Module that you linked me figured out, this seems like a much better way of sending email.

Thanks again for all your help to this point.

Is this of any use to you? I found it on the help thingy from microsoft.

http://support.microsoft.com/kb/260819