Upgraded to Access 2016 - VBA email sending not working (1 Viewer)

[atrium]

We have upgraded from 2010 to Access 2016 and my email routine is not working. Getting a runtime error - 2147417851 (800 10105)
Methos ".To' of object - _Mail Item Failed

This is the code I have been using in 2010

Public Function SendPDFemail()
 Dim strEmail As String
 Dim strMsg As String
 Dim oLook As Object
 Dim oMail As Object

 Set oLook = CreateObject("Outlook.Application")
 Set oMail = oLook.createitem(0)
 With oMail
 .To = Me.SendToEmailFld    '" emailaddress@sample.com.au"
 .body = "Dear Sir / Madam" & Chr(10) & Chr(10) & "Please find enclosed a self explanatory communication from The Group." & Chr(10) & Chr(10) & Chr(10) & Chr(10) & "Regards" & Chr(10) & Chr(10) & "The Group"
 .Subject = "This is a Communication from The Group for your attention  -  " & Me.ClientRefNoFld
 .Attachments.Add (DocPathFld & PdfFileNameFld)

 '********* What is the command to preview instead of send ****  .Display *****
 .Display

 End With

 Set oMail = Nothing
 Set oLook = Nothing
 End Function

The error stops on the .To item in the With statement

I suspect 2016 does this a little differently Not sure what library references I need. I have included the Microsoft Outlook 16.0 Object Library is there anything else?

Any help would be appreciated

atrium

 

[CJ_London]

you are using late binding so library references should not be required.

Think you need to do some testing - is SendToEmailFld populated?

Since you have the relevant outlook library, convert to early binding and see if that fixes the problem - is so, check 0 is the required value for createitem

 

[pbaldy]

Ran into this. Either use CStr() around the form reference or put it in a variable first.

 

[atrium]

Yes SendToEmailFld = "silly@sam.com" (just a test email set up for this example)
If I substitute "john@abc.com.au" for Me.sendToEmailFld it works perfectly?

When it gives me the error and I select debug and move my cursor over Me.sendToEmailFld it has "john@abc.com.au" in it ???

 

[atrium]

To Pbaldy,
Not sure what you mean by the form reference - Which line

Atrium

 

[sneuberg]

I think he means change

 .To = Me.SendToEmailFld

to

 .To = CStr(Me.SendToEmailFld )

 

[pbaldy]

That's correct.

 

[sneuberg]

@pbaldy

I'm curious about how this works. If you run this code with some data in a textbox named Text0

MsgBox VarType(Me.Text0)

you get the same result as

MsgBox VarType(CStr(Me.Text0))

namely 8 which according to this Web page is a string. So what is CStr doing in this case that stops this error.

 

[atrium]

Thanks everyone for your input.

Thanks PBaldy - it worked CStr(Me.SendToEmailFld)

 

[pbaldy]

Happy to help!

 

[CJ_London]

looks like this is another case where newer versions of access enforce stronger data typing

 

[pbaldy]

@pbaldy

I'm curious about how this works.

Sorry Steve, I missed this. I eventually solved it myself using a variable. During an online discussion of the issue the CStr() option was thrown out, which also works. It was suspected to be a typing issue as CJ suggested. Seems that the form reference was being seen as a control rather than a string value, and either using a variable declared as String or CStr() resolved it.

In my experience, it was more OS driven than Access version, but I didn't test a lot. I had existing apps that exhibited this error when users were upgraded to 8.1 or 10. I was told the Access version had been the same, but wasn't onsite so couldn't verify.

 

[sneuberg]

Sorry Steve, I missed this. I eventually solved it myself using a variable. During an online discussion of the issue the CStr() option was thrown out, which also works. It was suspected to be a typing issue as CJ suggested. Seems that the form reference was being seen as a control rather than a string value, and either using a variable declared as String or CStr() resolved it.

In my experience, it was more OS driven than Access version, but I didn't test a lot. I had existing apps that exhibited this error when users were upgraded to 8.1 or 10. I was told the Access version had been the same, but wasn't onsite so couldn't verify.

Thanks for your response. I still think you shouldn't have to do this and this should be considered a bug by Microsoft.

 

[Minty]

Maybe more luck than judgement, I've always put the outlook (and other) automation parameters into variables, as I'm certain there was a issue with referring to certain object types within the Outlook properties.

 

[CJ_London]

given the rationale for the explanation, just out of curiosity, if it can be tested does

.To = Me.SendToEmailFld.Value

work?

and to dot all the i's, is SendToEmailFld the real name of the control or is it something else that might be a reserved word? I have come across that as an issue before.

 

[pbaldy]

Forgot to mention it first happened to me with a recordset value, so this failed:

.To = rs!FieldName

the fix I came up with at the time (relevant lines only):

Dim strEmail As String
strEmail = rs!FieldName
.To = strEmail

In my case there were no reserved words involved, either when it was a recordset value or form reference.

 

[Cronk]

Instead of

Dim strEmail As String
strEmail = rs!FieldName
.To = strEmail

I've used
.To= rs!FieldName & ""

 

[The_Doc_Man]

If MsgBox VarType(Me.Text0) does not work but,...

MsgBox VarType(CStr(Me.Text0)) DOES work, ...

I think the problem might be that the output of CStr is always a string, and MsgBox definitely wants a string for that position, whereas Me.Text0 is a control, for which Me.Text0.Value is in all likelihood a VARIANT, which is not a string.

That's why using the Cxxx functions to re-cast the format of a value are so helpful. They pretty much guarantee the data type of what you are using.

 

[sneuberg]

If MsgBox VarType(Me.Text0) does not work but,...

MsgBox VarType(CStr(Me.Text0)) DOES work, ...

I think the problem might be that the output of CStr is always a string, and MsgBox definitely wants a string for that position, whereas Me.Text0 is a control, for which Me.Text0.Value is in all likelihood a VARIANT, which is not a string.

That's why using the Cxxx functions to re-cast the format of a value are so helpful. They pretty much guarantee the data type of what you are using.

I didn't mean to confuse this issue with the msgbox function. I was just using that to display the result of VarType which is telling me that the value from a textbox should not have to be converted to a string because it already has the type of String as indicated by the 8 that VarType returns.

If a programmer write the line as

.To = Me.SendToEmailFld

I can't see any flaw in that code. I think this is a bug that Microsoft should fix.

Another thing about textboxes is that if you do something like

Me.Text0 + Me.Text1

you get the concatenation of the values in the textboxes, i.e., they act like strings.

 

[The_Doc_Man]

sneuberg, I understand. Nonetheless, I have seen cases where due to strong type constraints, even a string isn't equal to a string - if they were qualified differently. The use of CStr "hides" the qualifier.