Sending Email with Late Binding Error Through VBA

Question

I'm trying to use a late binding to email from VBA in Access 2010 and Access 2003. 2003 gives me 'Could not complete the operation. Once or more paramet values are not valid.' and 2010 gives me 'Invalid procedure call or argument.' I've done the step through and it fails at .send near the bottom. Am I setting up my binding wrong? I'm trying to do this without using Microsoft Object Library in the References.

Thanks.

'Refers to Outlook's Application object
Dim appOutlook As Object
'Refers to an Outlook email message
Dim appOutlookMsg As Object
'Refers to an Outlook email recipient
 Dim appOutlookRec As Object

'Create an Outlook session in the background
Set appOutlook = CreateObject("Outlook.Application")

'Create a new empty email message
Set appOutlookMsg = appOutlook.CreateItem(olMailItem)

'Using the new, empty message...
With appOutlookMsg

strSQL = "SELECT Email FROM Employees WHERE " & sqlVar & " = True"
Set myR = CurrentDb.OpenRecordset(strSQL)

Do While Not myR.EOF
Set appOutlookRec = .Recipients.Add(myR!Email)
appOutlookRec.Type = olTo
myR.MoveNext
Loop

strSQL = "SELECT Email FROM Employees WHERE '" & user & "' = Username"    
Set myR = CurrentDb.OpenRecordset(strSQL)

Set appOutlookRec = .Recipients.Add(myR!Email)
appOutlookRec.Type = olCC

.Subject = wonum
.Body = "Department: " & strDep & vbNewLine & vbNewLine & _
    "Issue is at: " & strIssue & vbNewLine & vbNewLine & _
    "Priority is: " & strPriority & vbNewLine & vbNewLine & _
    "Complete by: " & strDate & vbNewLine & vbNewLine & _
    "Description: " & strDesc

.Send

End With

score 2 · Accepted Answer · answered Apr 12 '13 at 14:00

2

Without a reference, VBA will not know about Outlook constants such as olTo and olCC. With late binding (no reference), you must supply the values for constants rather than the names of the constants.

However, since you didn't report a compile error about those constants, that suggests your code module does not include Option Explicit in its Declarations section. Trying to troubleshoot VBA code without Option Explicit is a waste of time.

Add Option Explicit, then choose Debug->Compile from the VB Editor's main menu and fix anything the compiler complains about. Resume you troubleshooting afterward.

answered Apr 12 '13 at 14:00

HansUp

95,961
11
77
135

2

Good point. When I saw `.CreateItem(olMailItem)` I figured that there must be a `Const olMailItem = 0` somewhere else in the code. Using `Option Explicit` has been so automatic for me for so many years that I don't even think of it when I consider a problem. – Gord Thompson Apr 12 '13 at 14:14
I added Option Explicit and found a few errors. But what should I set olTo and olCC as? I set them as `Const olTO = 0` and it still gave me my same error after it compiled properly. (PS thanks for the explicit info) – Grant Apr 12 '13 at 15:03
1

From the OlMailRecipientType Enumeration: http://msdn.microsoft.com/en-us/library/office/ff865653.aspx – HansUp Apr 12 '13 at 15:07
Adding `Option Explicit` then making each `olMailItem, olTo, and olCC` a constant worked. Thanks for all the help! – Grant Apr 12 '13 at 15:12

score 1 · Answer 2 · answered Apr 12 '13 at 13:52

There is an article here on sending email via Outlook using early and late binding. In the "Late Bound Conversion Checklist" at the end, the last suggestion is

Add optional arguments that have a default value

I cannot vouch for that advice because when I need to send email messages from Access I use CDO, not Outlook. Still, it sounds like it might be worth a try.

Sending Email with Late Binding Error Through VBA

2 Answers2