Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Sending email from within Access 2003

I am using the http://support.microsoft.com/?kbid=286431 version of the VBA Code - however, there are two messages that pop up at the end of the code.

 How do i prevent this so it is completely automatic?  The first is check the box for "A program is trying to access e-mail address you have stored in outlook...., then I need to manually click on Allow Access" and the second is to click yes to "A program is trying to automatically send email on your bechalf - do you want to allow this......

Also I need to have my message be displayed on multiple lines how do I break up the body of my email to display correctly.

Thanks,

Karen

Here is my code:

ub SendMessage(Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment

   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add("Karen Schaefer")
      objOutlookRecip.Type = olTo
' .Recipients.Add("Mark Olson")
      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("Karen Schaefer")
      objOutlookRecip.Type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Master Reference Table"
      .Body = "Latest data is ready for your input. & "" &  vbCrLf & vbCrLf" & _
                "Thanks & "" & vbCrLf & vbCrLf" & _
                "Karen"
      .Importance = olImportanceHigh  'High importance

      ' Add attachments to the message.
     ' If Not IsMissing(AttachmentPath) Then
      '   Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      'End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub
Avatar of LenaWood
LenaWood

Here is a link that should assist you with part of your issue:

https://www.experts-exchange.com/questions/21405469/a-program-is-trying-automatically-send-e-mail-on-your-behalf.html

Here is another link to help with another part of your question

http://office.microsoft.com/en-us/assistance/HA011127891033.aspx

HTH
Lena

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here's a lengthy MS article concerning your issue:

http://msdn.microsoft.com/office/default.aspx?pull=/library/en-us/odc_OL2003_ta/html/odc_olSendEmailsProgrammatically.asp

lots of fixes in here, but I've never really tried any
I am not sure how to break up your message for sure.  How I do it is that I have Access generate the message for me on a form (where I am clicking the send the email button).  How I do it is when I click the button, I have Access put the message in an unbound textbox and then use that text box as my message.  All the carriage returns seem to carry over to the body of the email.

HTH
Lena
Avatar of Karen Schaefer

ASKER

Final solution - does not correct the issue with the popup messages - for security purposes.
Option Compare Database
Option Explicit
                   

Sub SendMessage(Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment
   
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
       ' SendKeys [TAB], [Tab}, "~", True
     Set objOutlookRecip = .Recipients.Add("Mark Olson")
                           .Recipients.Add ("Karen Schaefer")
      objOutlookRecip.Type = olTo
  '
      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("Karen Schaefer")
      objOutlookRecip.Type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Master Reference Table"
        .body = "Latest data has been entered into the Master " & _
                "Reference table, please make your chanees." & vbCrLf & _
                vbCrLf & "Thanks," & vbCrLf & vbCrLf & "Karen"
      .Importance = olImportanceHigh  'High importance

     
      ' Add attachments to the message.
     ' If Not IsMissing(AttachmentPath) Then
      '   Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      'End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub
Try using CDO, see if that makes a difference. I think it does not come up with the security message

e.g. Ive something here that parameterises it
remember to setup your smtp server (see comment below)


Public Function SendEmail(ByVal strTo As String, _
                          ByVal strMessage As String, _
                          ByVal strSubject As String, _
                          Optional ByVal strAttach As String)

    Dim objEmail As Object
   
   
    On Error Resume Next
   
    Set objEmail = CreateObject("CDO.Message")

    '**** email address of sender
    objEmail.From = "fred@smith.com"      
    objEmail.To = strTo
    objEmail.Subject = strSubject
    objEmail.TextBody = strMessage
    objEmail.AddAttachment strAttach
    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    '**** smtp.xxx.com - here u enter your smtp server name, whatever that is
    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.xx.com"

    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    objEmail.Send
    If Err.Number <> 0 Then
        MsgBox "Error in sending. " & Err.Description
    Else
        MsgBox "Sent"   'remove this if u dont want confirmation
    End If
    Set objEmail = Nothing

End Function




A working example can be found here  https://filedb.experts-exchange.com/incoming/ee-stuff/111-CDO.zip

remember to setup your reference to the CDO object library
Where do I find my SMTP information -  I am not the administrator of my machine.

Karen
Thanks for all the suggestions - I ended up using the CheckYes addin suggested by LMS Consulting.

Thanks,