Sending email from Access via Outlook 1

[merlynsdad]

I'm trying to send automated email from Access 2003 via Outlook 2003 SP3. I know there's a problem with Outlook's security that pops up 4 user intimidating messages. I'm using the following code, and wondering if I put a SendKeys command in, would it solve this problem.

Public Sub SendEMail()
        
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
        Dim EnterKey As String
        
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        With objOutlookMsg
            DoCmd.SetWarnings False
            Set objOutlookRecip = .Recipients.Add("recipient@company.com")
            objOutlookRecip.Type = olTo
            .Subject = "Notification"
            .Body = "This is a test."
            .Importance = olImportanceNormal  'Normal importance
                For Each objOutlookRecip In .Recipients
                    If Not objOutlookRecip.Resolve Then
                        objOutlookMsg.Display
                    End If
                Next
            .Send
        End With
        DoCmd.SetWarnings True
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookRecip = Nothing
        Set objOutlookAttach = Nothing
        
        End Sub

If the square peg won't fit in the round hole, sand off the corners.

 

[vbajock]

You can get around it by setting Outlook's Macro Security level to "low", the thing is an anachronism from the pre-firewall days of yore, as long as your network is firewalled and a good corporate virus program is in place, there is absolutely no harm in doing this. If you can't do that, the next option is install a signed certificate. This link should help with that:

http://www.4tops.com/accessmailmerg...nd_email_without_outlook_security_warning.htm

 

[merlynsdad]

Actually I just found newer code, which only pops up one scary warning instead of four. Users can probably handle pressing "yes" once. Here's the newer code.

<StartCode>
Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = "Your Mail"
.Subject = "Subject"
.HTMLBody = "Text"
.send

End With

MsgBox "Operation completed successfully"

<EndCode>‎

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

You could add a Reference to your Project: Microsoft CDO for Windows 2000 Library and use something like this (no warning messages):

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)

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 = "somebody@msn.com"
    .Subject = strSubject
    .TextBody = strTextBody
    
    If Len(strAttachDoc) > 0 Then
        .AddAttachment strAttachDoc
    End If
    
    With .Configuration.Fields
        .Item(CDO.cdoSMTPServer) = "ABCD.XYZ.BUI.LAN"
        .Item(CDO.cdoSMTPServerPort) = 25
        .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
        .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With
    .Send
End With

Set objMessage = Nothing

End Sub

You just need to change SMTPServer name

Have fun.

---- Andy

 

[vbajock]

I absolutely agree with Andy and that's how I code all my email enabled applications, the CDO object is the way to go, it is a little more of a learning curve than using the Outlook code model, but the end product is vastly superior. Google "CDO Access VBA", tons of example code on the net.

 

[merlynsdad]

Thanks for the good ideas, but it's a government system, so playing around with the server is not an option.

If the square peg won't fit in the round hole, sand off the corners.

 

[vbajock]

You're not "playing around with the server" you're routing the email thru ports that are already in use to deliver email from your client, in effect you are just jumping over Outlook to the same port Outlook is using. You can get the name of the email server by looking at the Outlook mail profile on the client computer using Control Panel.

 

[Andrzejek]

Hey merlynsdad, I also work for the government and by using this approach I made 'government' (and people using the system I work on) very happy

Have fun.

---- Andy