Automating an email in MS access 2003

Topic

New Reply

Hey loungers!

I have recently got this error when testing an bit of code that creates a snapshot of a report, and attaches it to an email with a message/ address (filled out by entry onto a form)

however, since writing the code, my office suite has been “upgraded” to 2007. (however i still use access 2003 for compatibility reasons!) i used the following code:

Code:

Dim OL As Outlook.Application, OI As Outlook.MailItem, OA As Outlook.Attachments
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim intreply As Integer
Dim strEmail(3)

Set OL = Outlook.Application
[color color="#FFFF00"]Set OI = OL.CreateItem(olMailItem)[/color]


'Create SnapShot
DoCmd.RunMacro "McrPulmonaryRehab"



'Create Email
OI.Recipients.Add "" & Me.cboto & ""
OI.CC = "" & Me.cbocc & ""
OI.BCC = "" & Me.cbobcc & ""
OI.Subject = "" & Me.txtsubject & ""

OI.Body = "" & Me.txtmsg & "" & vbCrLf & vbCrLf & "" & "Downloaded on " & Date
Set OA = OI.Attachments
OA.Add "\charliedatabasesPulmonary RehabPulmonary Rehab ReportsPulmonaryRehabReport" & "" & Month(Now()) & "" & ".snp", olByValue, 1, "Pulmonary Rehab Data " & Date

'Send Email
OI.Send

'Add records into the Audit Table
strEmail(1) = Me.cboto.Value
strEmail(2) = Me.cbocc.Value
strEmail(3) = Me.cbobcc.Value


Set rst = dbs.OpenRecordset("tblEmailAudit ")

i = 1

Do While i <= 3
rst.AddNew

rst!Address = strEmail(i)
rst!Date = Now()
rst.Update
Response = acDataErrAdded

i = (i + 1)

Loop

'Tidy up

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

On executing the highlighted line i get the following error "Runtime error 13, Type Mismatch"

As everyone in my office now uses 2007, i have no way of testing this theory, but, am i right in assumign that it is an issue with outlook 2007? I have used this code before, and it worked fine with no problems. the reasoning behind my theory is that the only thing to really change is the installation of office 2007.

if this is the case can i be certain that it would work when i pass it onto the client, who uses office 2003?

Thank you for your time.

Reply | Quote

Replies

Unfortunately, I don’t think it is an Outlook 2007, assuming that you have your Office 2007 current with updates (SP2). We use almost identical code across both Outlook 2003 and 2007 and don’t have issues with it. I presume the line that has the color tags on it is the one you were trying to highlight – unfortunately code boxes don’t allow any text formatting. I think the issue is with the prior to that. The one difference I see between your code and what we use is that the line prior to that would be

Code:

Set OL = CreateObject("Outlook.Application")

I’m not positive that will resolve your issue, but give it a try.

One other note regarding the use of the “snapshot” format – it is still supported in Access 2007, but Access 2010 no longer supports the snapshot format and offers PDF and XPS as replacements – see Changes in Access 2010 for details.

Reply | Quote

As we are the only 7 people in the trust to have access 2007, and everyone else uses 2003 i doubt we need to worry about 2010 just yet. Should we ever make the switch i doubt this system would still be used then.

It executes that line no problem at all, i will change it, as, like you say it might be an issue.

The 2007 is just a complication for while i am using it.

The end user will have outlook 2003 as well as access 2003, and i have had this code working in that circumstance, it is just that now all of a sudden since this upgrade it doesnt work.

Reply | Quote

I just ran this with Access 2007 (and Outlook 2007) with WendellB’s modification. It ran just fine.

I do have a question though. How can I send an Outlook email with preview (that is, don’t send it, but just prepare the email)? I’d like to test sending to many recipients w/o actually sending.

Reply | Quote

I believe all you have to do is to to replace the actual “Send” command line with a command that makes the prepared message visible. I’m doing this from memory (definitely flakey) so test to see what you get, but I believe we have done that where we wanted the sender to have the option to edit the email before it was sent.

Reply | Quote

WendellB: Thanks. It is the Outlook Item .display method. Thanks.

Reply | Quote

I tried it, and i recieved an error:

Run-Time error ‘429’
ActiveX component can’t create object

if it makes a difference, i might not have been clear, in that im using access 2003, but have outlook 2007 installed.

Reply | Quote

I suspect you may have references problems in your database. You should have a reference to Outlook 11 in your VBA project – however you can only have one version of Outlook installed and running, so your reference is probably to Outlook 12. The code I use runs cleanly on both Office 2003 and Office 2007, but I have not tested it in an environment with two or more versions of Office installed.

Reply | Quote

yes, i am using outlook 12, so technically it should work on a pc that has outlook 11? there is a very good chance that the 2003 computer i tried it on was using something older.

Reply | Quote

Actually, only if your reference is to Outlook12 should it work. If the older PC has Outlook11, but the reference is to Outlook 12, it will fail. It will work the other way typically – i.e. you have a reference to Outlook11 but the PC had Outlook12 – the PC will adjust the reference for you.

Reply | Quote