Karen Schaefer
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.Appl ication")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMa ilItem)
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(Attachmen tPath)
'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
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.Appl
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMa
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(Attachmen
'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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
HTH
Lena
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.Appl ication")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMa ilItem)
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(Attachmen tPath)
'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
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.Appl
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMa
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(Attachmen
'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.Fie lds.Item(" http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'**** smtp.xxx.com - here u enter your smtp server name, whatever that is
objEmail.Configuration.Fie lds.Item(" http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.xx.com"
objEmail.Configuration.Fie lds.Item(" http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fie lds.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
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.Fie
'**** smtp.xxx.com - here u enter your smtp server name, whatever that is
objEmail.Configuration.Fie
objEmail.Configuration.Fie
objEmail.Configuration.Fie
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
ASKER
Where do I find my SMTP information - I am not the administrator of my machine.
Karen
Karen
ASKER
Thanks for all the suggestions - I ended up using the CheckYes addin suggested by LMS Consulting.
Thanks,
Thanks,
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