[Solved] Send mail from VBA (1 Viewer)

[disgracept]

Good morning!

I'm having a little problem...
I want to send a mail from VBA in access as part of a "forgot password" routine.
Basically i want to send a mail with a random 4 digit code to ensure that the person who is asking the pass change is the one who owns the account.
The part of the random code is easy. But don't really sure how to implement the send mail routine.

I've searched a lot but, honestly, don't think the options i found are the best to use in access 2019:
- SendObject method - Very easy but it doesn't allow html content and need an email client to be installed;
- Outlook object model - Needs outllok to be installed;
- CDO - doesn't require an email client but, as far as i saw it's deprecated and microsoft doesn't recommend using it (just like with MAPI).

So my question is what should i use in your opinion?
Is there another way to do this other than the ones i metioned?

Thanks a lot for the help!
Cheers

 

[vba_php]

hmmmm....are you *sure* MS has said CDO is outdated? that was reliable for quite some time. what page did you get that advice from? it is noteworthy to say that most large corporations don't know anything about anything. (one man's opinion, of course).

why don't you have outlook installed in 2019? It would seem to me that it would be part of the office package by default. is it not? I believe I've used SendObject() before, but I think that was only for a snapshot of a report. have you seen this FAQ that Colin posted a long time ago? https://www.access-programmers.co.uk/forums/threads/cdo-email-tester.293368/

I would seriously doubt that would *not* work. Why not give it a try? I remember looking at it, and I don't believe it uses outlook in anyway. But you might want to check to make sure I'm right about that. If I remember right, CDOsys is a DLL resource, not a VBA reference like "MS OUTLOOK xx.0".

As far as MAPI is concerned, I would seriously challenge MS on that one, if they are claiming it's out of date or anything else. That's just like the IMAP/POP3 phenomenon from google's instructions when syncing outlook to it. Sometimes it works, sometimes not. It depends on literally so many factors that are undocumented between MS and Google, it leaves the user to figure out the stuff on their own, which of course is no surprise.... hopefully this will help a bit.

 

[theDBguy]

Hi Carlos. Just curious, if your users don't have an email client installed, how would they send out any email otherwise? If you want your Access application to use your own mail server, then you could try to build a web service that you can communicate with using VBA. Just a thought...

 

[disgracept]

Thanks for the reply...

hmmmm....are you *sure* MS has said CDO is outdated? that was reliable for quite some time. what page did you get that advice from? it is noteworthy to say that most large corporations don't know anything about anything. (one man's opinion, of course).

Well, i saw this here:
https://support.microsoft.com/en-us...s-cdo-1-2-1-is-not-supported-with-outlook-201

That's why i said it was deprecated...

why don't you have outlook installed in 2019? It would seem to me that it would be part of the office package by default. is it not?

In theory all the workstations have outlook installed... But it's in theory...
And this approach would need an account to be created in the exchange server specifically for this since it needs to authenticate with the server to send the message.
Not sure if the IT department will agree.

I believe I've used SendObject() before, but I think that was only for a snapshot of a report. have you seen this FAQ that Colin posted a long time ago? https://www.access-programmers.co.uk/forums/threads/cdo-email-tester.293368/

This will send a plain text message. I wanted for the code to outstand from the rest of the text...

As far as MAPI is concerned, I would seriously challenge MS on that one, if they are claiming it's out of date or anything else. That's just like the IMAP/POP3 phenomenon from google's instructions when syncing outlook to it. Sometimes it works, sometimes not. It depends on literally so many factors that are undocumented between MS and Google, it leaves the user to figure out the stuff on their own, which of course is no surprise.... hopefully this will help a bit.

Here maybe i'm wrong indeed...
https://support.microsoft.com/en-us...ces-between-cdo-simple-mapi-and-extended-mapi
I can use Simple MAPI in VBA. but, again, will need an account like in Outlook Object Model...

 

[vba_php]

Hi Carlos. Just curious, if your users don't have an email client installed, how would they send out any email otherwise?

ha ha! good point! maybe they're using an internet-based client that gets used with "mailto:" links set in the windows CP?

 

[disgracept]

Hi Carlos. Just curious, if your users don't have an email client installed, how would they send out any email otherwise? If you want your Access application to use your own mail server, then you could try to build a web service that you can communicate with using VBA. Just a thought...

Indeed... If i send a mail message then they MUST have outlook installed to see it...
Man i was a Homer Simpson here... Duuhhh...

in regard to the web service it could be a solution... but honestly i don't have a faint idea how to implement it... will research...

 

[vba_php]

Here's what I think, disgracept....

Well, i saw this here:
https://support.microsoft.com/en-us...s-cdo-1-2-1-is-not-supported-with-outlook-201

That's why i said it was deprecated...

per MS's words, sir:

Although the Collaboration Data Objects (CDO) 1.2.1 object library could be used with Microsoft Outlook 2010 and later verisons, we do not recommend or support this in any way.

this is a classic line from MS, and it should be taken with a grain of salt, literally. just because they *recommend* not something, doesn't mean it won't work just fine. they've got years of reputation saying stuff like this for products that make them no money. again, just my observation.

In theory all the workstations have outlook installed... But it's in theory...
And this approach would need an account to be created in the exchange server specifically for this since it needs to authenticate with the server to send the message.
Not sure if the IT department will agree.

I did a contract with an energy company in a nearby town 1 month ago and my boss was a Python guru, and it took hi 10 years to pull the IT dept. out of their paranoia shell and implement his efficiency solution!

This will send a plain text message. I wanted for the code to outstand from the rest of the text...

not sure I'm following that one....

Here maybe i'm wrong indeed...
https://support.microsoft.com/en-us...ces-between-cdo-simple-mapi-and-extended-mapi
I can use Simple MAPI in VBA. but, again, will need an account like in Outlook Object Model...

per the link:

The Simple MAPI functions can be called from any application that supports both making API calls as well as the structures and data-types used by Simple MAPI, such as C, C++, Visual Basic, and Visual Basic for Applications (VBA).

and:

"The MAPI controls included with Microsoft Visual Basic (versions 3 through 6), and the version of MAPI installed by Outlook Express implement Simple MAPI only."

so it seems like it might work fine. VB 6.0 specifically is very similar to VBA.

 

[theDBguy]

Indeed... If i send a mail message then they MUST have outlook installed to see it...
Man i was a Homer Simpson here... Duuhhh...

in regard to the web service it could be a solution... but honestly i don't have a faint idea how to implement it... will research...

The email has to be processed by something, that's the job of the mail server. So, even if you don't want to use an email client, you cannot avoid using a mail server. If so, I was assuming you must have one hosted on the web that you can connect to using VBA.

 

[theDBguy]

ha ha! good point! maybe they're using an internet-based client that gets used with "mailto:" links set in the windows CP?

If users can click on a mailto link, then I would guess Carlos might be able to construct a mailto link to include a HTML message.

 

[disgracept]

Thanks for all your inputs!

I think i'll try the OOM and see how it goes... I can always use my own exchange account to send the mails...

I'll try to code it and, if i get stuck, i'll come back and ask again if you don't mind...

 

[vba_php]

I was assuming you must have one hosted on the web that you can connect to using VBA.

I'm pretty VBA is limited in its ability to do these kinds of things, isn't it guy? How much do you know about this? VBA isn't the most resource-rich language in the world. I mean, compared to something like Python, it's on a backburner 1,000 miles away.

If users can click on a mailto link, then I would guess Carlos might be able to construct a mailto link to include a HTML message.

I would tend to think so.

 

[theDBguy]

I'm pretty VBA is limited in its ability to do these kinds of things, isn't it guy? How much do you know about this? VBA isn't the most resource-rich language in the world. I mean, compared to something like Python, it's on a backburner 1,000 miles away. I would tend to think so.

I use VBA to connect to web services all the time. The web service could be using PHP or Python, but VBA can send and receive queries to/from them.

 

[vba_php]

I use VBA to connect to web services all the time. The web service could be using PHP or Python, but VBA can send and receive queries to/from them.

got an example of that? LI? github? your website?

 

[theDBguy]

got an example of that? LI? github? your website?

You could start here.

 

[vba_php]

You could start here.

good stuff. nice link. thanks. i found this of interest:

The following is a list of the properties and methods of the XMLHttp class commonly used when making a non asynchronous request from a VBA client.

reminds me of async's beginnings with XMLHttpRequest(), replaced by the hugely popular fetch(). so when you use this stuff, guy, what exactly do you see? I ask because the page says:

used when making a non asynchronous request from a VBA client

is it talking about activity inside of access or stuff you can't see happen like the execution jQuery on this forum?

 

[disgracept]

Well guys i tried using OOM and CDO...
Both work...

Here is the code i used for OOM:

  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem

  Set OutlookApp = New Outlook.Application
  Set OutlookMail = OutlookApp.CreateItem(olMailItem)

  With OutlookMail
    .BodyFormat = olFormatHTML
    .HTMLBody = "<p style='text-align: center;'>Caro(a) user,</p>" & _
                "<p style='text-align: center;'>Para repôr a sua Password utilize, por favor, o seguinte c&oacute;digo:</p>" & _
                "<h2 style='text-align: center;'><strong>0000</strong></h2>"
    .To = "something@someserver.pt"
    .Subject = "Reposição de Password"
    .Send
  End With

But this sends the mail from the account that's configured in the local outlook, and there is no "From" option as far as i saw...

The CDO version is:

    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "someone@someserver.pt"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "somePassword"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With

    strbody = "<p style='text-align: center;'>Caro(a) user,</p>" & _
              "<p style='text-align: center;'>Para repôr a sua Password utilize, por favor, o seguinte c&oacute;digo:</p>" & _
              "<h2 style='text-align: center;'><strong>0000</strong></h2>"

    With iMsg
        Set .Configuration = iConf
        .To = "anotherone@someserver.pt"
        .From = """Me"" <me@someserver.pt>"
        .Subject = "Reposição de Password"
        .HTMLBody = strbody
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing

I used gmail because i didn't want to ask for the exchange server info just yet.
It worked but i had to turn on the "Less secure app access" option in the gmail account...

BTW can i configure the mail in a way that there is no "Reply-to"?

 

[vba_php]

But this sends the mail from the account that's configured in the local outlook, and there is no "From" option as far as i saw...

are you sure there is not a .from property associated with the outlook mail object? it doesn't make sense that there wouldn't be, to be honest.

BTW can i configure the mail in a way that there is no "Reply-to"?

more than likely you can, because all other languages have the ability to specify this. for instance, in PHP, I've used this spec:

    $headers = "From: no-reply@OwnersDomain.com\r\nReply-To: $email\r\nReturn-Path: $email\r\n";
    mail($myemail, $email_subject, $email_body, $headers);

and in that header, everything is optional. I've never sent an email without a "reply-to" spec in it, so I have no idea what would show up as a default address. More than likely it would say something like this (if coming from GoDaddy's email server):

p3plcpnl0398.prod.phx3.secureserver.net

other web languages also offer additional mailing options using class libraries, for a variety of reasons. for instance, in PHP, this is from github:

$mail = new PHPMailer;
$mail->isSendmail();
$mail->setFrom("no-reply@domain.com", "Business Name Corporate");
$mail->addReplyTo($email, $firstname . " " . $lastname);
$mail->addAddress("corp@domain.com", "Business Owner");
$mail->Subject = "Contact Form Submission Notification";

I would assume you could do the same thing in almost every other language. But then again, I still suspect that VBA is trailing behind, even though theDBguys' link suggests otherwise. Might want to check it out!

 

[disgracept]

are you sure there is not a .from property associated with the outlook mail object? it doesn't make sense that there wouldn't be, to be honest.

Well... I'm not tottaly sure but intelissence doesn't show it...

more than likely you can, because all other languages have the ability to specify this. for instance, in PHP, I've used this spec:

I can specify one!
The default is the message sender, if you don't specify another one.
But if, for example, i put "no-reply@google.com" will it work if i don't have such account created there?

 

[The_Doc_Man]

First, regarding CDO: The article you quoted was of a narrower scope than you might first have thought from the headline and the first paragraph. In that article I found this:

Important: This article applies to using CDO 1.2.1 client-side together with Outlook 2010 and later versions. It does not apply to using CDO 1.2.1 that installs the MAPI subsystem and that is designed for use on a computer without Outlook.

Since you are working with computers without Outlook, I think you are OK. I believe the issue they are discussing is some kind of conflict between CDO and Outlook, not anything to do with use of MAPI.

Second, because CDO and Outlook CAN use SMTP to send things, there will be support for typical message properties such as .ReplyTo, .From, etc. ... but sometimes they use another name for the field to avoid a naming conflict. And in fact it is possible that the conflict described in the article has to do with message property naming when you have a mixed method of sending in the same app.

Because of naming changes to avoid conflicts, you might actually see .Sender rather than .From (and might actually see both properties depending on which package you are using).

 

[disgracept]

Second, because CDO and Outlook CAN use SMTP to send things, there will be support for typical message properties such as .ReplyTo, .From, etc. ... but sometimes they use another name for the field to avoid a naming conflict. And in fact it is possible that the conflict described in the article has to do with message property naming when you have a mixed method of sending in the same app.

Because of naming changes to avoid conflicts, you might actually see .Sender rather than .From (and might actually see both properties depending on which package you are using).

In CDO there is a ReplyTo property... there isnt't in the OOM...
But there's in fact a sender one... Unfortunately it's only used in a session where multiple accounts are defined in the profile. You can't assign an external mail here...