Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

03Explorer

Technical User
Sep 13, 2005
304
US
[Bold]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.[/bold]

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 [BOLD]Allow/Deny button[/bold] 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
 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
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
 
>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.
 
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.
 
> 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 ...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top