×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA Send Outlook Email from MS Access (avoid user prompt to allow)

VBA Send Outlook Email from MS Access (avoid user prompt to allow)

VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
I know the title sounds very suspicious and the feature is valid for protection, but I need to bypass it for my internal app so the system can send team members emails with generated attachments.

I am using Office 365, I am building an app in Access that processes data, then attaches an Excel file to an email. All is working except for the send email. It prompts the user with "A program is trying to send an email message on your behalf. If this is unexpected, click Deny and verify your antivirus software is up-to-date." ... prompt waiting for Allow/Deny button to continue processing.

CODE

Sub SendEmail(DirLocation)

    Dim appOutLook As Outlook.Application   '- Outlook Application
    Dim MailOutLook As Outlook.MailItem '- Outlook Mail
    Dim objFSO As Object        '- File System Object
    Dim objFolder As Object     '- Folder
    Dim objFile As Object       '- File
    Dim strFldr As String          '- Folder Name
    Dim strNme As String           '- Original File Name
    Dim strPth As String           '- Original File Path
    Dim strDpth As String          '- Destination File Path

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strFldr)
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.createitem(olMailItem)
    Set db = CurrentDb
    Set objFolder = objFSO.GetFolder(strFldr)
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.createitem(olMailItem)
    Set db = CurrentDb
    strFldr = DirLocation
    strNme = objFile.Name  '--file name

        If strNme <> "" Then
            Set MailOutLook = appOutLook.createitem(olMailItem)  '-- Intiate Outlook to create a new email message
            With MailOutLook                            '-- Define and populate Email message
                .Display
                .BodyFormat = olFormatRichText
                .To = "RobertWagner@SullivanCotter.com" '-- Testing purposes
                .Subject = "Subject text here"
                .HTMLBody = "Body text here"
                .Attachments.Add strPth & strNme   '-- Attachment #1 (File)
                '- I want to understand options for .SendUsingAccount for speicific vs a Number(3)
                .SendUsingAccount = appOutLook.Session.Accounts.Item(3)  '- third associated email address
                .Send
            End With
        Else
            MsgBox "No file matching " & strPth & strNme & " found." & vbCrLf & _
                    "Processing terminated."
            Exit Sub    'This line only required if more code past End If
        End If
End Sub 

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)


You have some information (and solution) about it here


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Thanks Andy,

I am looking for a method that doesn't need an installed add-on or SendKey action. I seem to believe there is an administrative way around this (also avoiding going into Outlook and turning off Trust Center/Programmatic Access warning.

=\

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

Have you considered by-passing Outlook all together and send e-mails straight to Exchange server?
This way you have full control of what's going on.

I have code to do it, if you want...

Have a reference to:
Microsoft CDO for Windows 2000 Library

CODE

Dim objMessage As CDO.Message

Public Sub SendAMessage(strFrom As String, strTo As String, _
    strCC As String, strSubject As String, strTextBody As String, _
    Optional strBcc As String, Optional strAttachDoc As String, _
    Optional blnHighPriority As Boolean = False)

Set objMessage = New CDO.Message

With objMessage
    .From = strFrom
    .To = strTo
    If Len(Trim$(strCC)) > 0 Then
        .CC = strCC
    End If
    If Len(strBcc) > 0 Then
        .BCC = strBcc
    End If
    ''' On behalf of
    '.Sender = "vvv@domain.com"
    
    If blnHighPriority Then
       With .Fields
           ' for Outlook:
           .Item(cdoImportance) = cdoHigh
           .Item(cdoPriority) = cdoPriorityUrgent
    
           ' for Outlook Express:
           '.Item("urn:schemas:mailheader:X-Priority") = 1
    
           .Update
       End With
    End If
    
    .Subject = strSubject
    
    If InStr(UCase(strTextBody), "<HTML>") Or InStr(UCase(strTextBody), "</HTML>") Then
        .HTMLBody = strTextBody
    Else
        .TextBody = strTextBody
    End If

    If Len(strAttachDoc) > 0 Then
        .AddAttachment strAttachDoc
    End If
    
    With .Configuration.Fields
        .Item(CDO.cdoSMTPServer) = "NTSMTP.COM.ABC.XYZ"
        .Item(CDO.cdoSMTPServerPort) = 25
        .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
        .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With
    .Send
End With

Set objMessage = Nothing

End Sub 


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Ohh, Thanks Andy!

Would you know if there is something the Exchange server administrator will need to adjust? (for your suggestion)

Rob

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

Yes.
When I was implementing this logic, I could send several e-mails pretty quick. Mail server assumed this was SPAM and locked me out. Admin had to allow my application to sent multiple e-mails and not stop it. I was just too efficient, I guess... ponder


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Thanks again Andy for sharing the CDO concept.

I updated your code and get the response "The transport failed to connect to the server". Thinking need to keep searching for another option that is self contained vs buy a plugin (https://www.add-in-express.com/creating-addins-blo...)

=\

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

I have the add-in-express toolset. It allows you to create some nicely integrated stuff. You might consider Redemption. It looks pretty robust: http://www.dimastr.com/redemption/home.htm

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Thanks, DjangMan... I am looking into your suggestion. The first glimpse looks good!

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

I hope you updated this (red) part of code:
.Item(CDO.cdoSMTPServer) = "NTSMTP.COM.ABC.XYZ"
with your server info...


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Yes Andy, I am doing some digging to find out what our Exchange or SMTP server Name is... Trying to fly under radar vs ask and get the red tape. =)

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Do you know if there is a trick when using Office365 as mail routing? Authentication?

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

We moved to Office365 (I believe) and I did not have to do anything.
My code still works just fine....

SMTP server Name try this.


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

Did you Google "find your smtp server name"?
There are a lot of hints of how to find it...


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Andy, I am back on topic for a bit today... I broke down and talked with IT support. They gave me the SMTP Server information without any hitches! Sounds too good to be true, but I hope to update with positive feedback soon! :)

Rob

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

That's beyond my knowledge sad
I had this code from somewhere, put my 'stuff' in there and it worked great. So I've never questioned what's what. It just worked. And I shared this code with some people here on TT and (eventually) it worked form them, too.

So, give it a try, modify if needed. Good luck.
Share your experience here, especially when it starts to work for you, too smile


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Andy, I notice other postings online for CDO, code commonly passes an ID and PASSWORD. I'm asking because I didn't see it in your code which implies your version is not using any authentication. This aligns with my discussion with IT that no need to authenticate when within the Local Network.

Thanks for your honesty and sharing! I will do my best to keep the thread updated with my findings. =)

Rob

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Ahhh, Andy! I found the source of my dillema.

CODE --> VBA:MS_Access

Public Sub SendAMessage(strFrom As String, _
                        strTo As String, _
                        strCC As String, _
                        strSubject As String, _
                        strTextBody As String, _
                        Optional strBcc As String, _
                        Optional strAttachDoc As String, _
                        Optional blnHighPriority As Boolean = False)

    Set objMessage = New CDO.Message
    
    With objMessage
        .From = strFrom
        .To = strTo
        If Len(Trim$(strCC)) > 0 Then
            .CC = strCC
        End If
        If Len(strBcc) > 0 Then
            .BCC = strBcc
        End If
        ''' On behalf of
        '.Sender = "vvv@domain.com"
        
        If blnHighPriority Then
           With .Fields
               ' for Outlook:
               .Item(cdoImportance) = cdoHigh
               .Item(cdoPriority) = cdoPriorityUrgent
        
               ' for Outlook Express:
               '.Item("urn:schemas:mailheader:X-Priority") = 1
        
               .Update
           End With
        End If
        
        .Subject = strSubject
        
        If InStr(UCase(strTextBody), "<HTML>") Or InStr(UCase(strTextBody), "</HTML>") Then
            .HTMLBody = strTextBody
        Else
            .TextBody = strTextBody
        End If
    
        If Len(strAttachDoc) > 0 Then
            .AddAttachment strAttachDoc
        End If
        
        With .Configuration.Fields
            .Item(CDO.cdoSMTPServer) = "SMTP.SOMETHINGoutlook.com"  '- Use TLS
            .Item(CDO.cdoSMTPServerPort) = 25      '- 587, 25  or 465
            .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
            .Item(cdoSMTPConnectionTimeout) = 10
            .Item(cdoSMTPUseSSL) = True
            
            .Update
        End With
        .Send
    End With

    Set objMessage = Nothing

End Sub 

Solution to the problem? Spend a few days with IT staff to figure out what layer of network protection is blocking as well as rules for the SMTP server. For me it was a two-fold issue: SMTP server needed to allow bounce off and McAfee Firewall had blocking rules as a double up of network protection.

Today it officially works (as I provided the code above)
*Note to others... no authentication is needed and populate your OWN SMTP server address.

Rob

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

Alright Rob!!!

Good for you thumbsup2 - keep digging and bothering IT personnel until you get what you want. smile


---- Andy

There is a great need for a sarcasm font.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

Hey, Rob,

Don’t ya think that Andy deserves a STAR? He ceatainly stuck with you and saved your bacon over the past 2 weeks+!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
Okay, I am back at the table with the original question about using Outlook for sending these attachments. IT identified the CDO method won't work in the production world. Not sure of their reasoning other than they said for my building it was fine but now looking at the company roll out, it is not feasible.

Is there any coding workaround for the outlook security. (Thinking not, because MS put that there for a reason)
What third party applications have you used and are comfortable suggesting I look at.

Much thanks!
Rob

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

>the CDO method won't work in the production world

<ahem> The 'CDO Method' is, in fact, SMTP. Are you saying that your IT team cant let you use SMTP? Or are they confused, and think that you are trying to use the old CDO 1.2, and thus trying to talk Simple MAPI direct to the Exchange server.

Plenty of applications use SMTP to send internal alert emails etc. And whilst there are certain security concerns about that, it seems odd that IT have tried to suggest it doesn't work.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

Ditto to strongm!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

(OP)
I believe the reason for the CDO method option being pushed off the table by the IT team is that they made an exception to their Firewall/Anti-Virus and elsewhere. Which to manage a company that is located all over the US using VPNS could be their logic.

They didn't let me see behind the curtain enough to assess their reasoning. But making the adjustments so I could use CDO was some hoops and triggers they had to adjust.

RE: VBA Send Outlook Email from MS Access (avoid user prompt to allow)

> But making the adjustments so I could use CDO

They have to do nothing for CDO. They do have to do something, probably, to allow traffic over port 25. And that's about it. Just sounds to me like they don't want to enable port 25 between you and the SMTP gateway on your Exchange Server. But there you go. Some IT departments can be odd …

And Redemption, as mentioned above, is pretty good if you need access to MAPI (which bypasses Outlook's security model, whilst mimicking much of Outlook's object model - which means code is fairly easily ported). However, the irony of this is your IT department are forcing/encouraging you to use a tool that deliberately bypasses security ...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close