Of course if that works you now have hard coded your e-mail into the
application so some more work needs to be done to get your e-mail into
the database then into the record set you are using ....
Bob Peterson
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robert
Peterson
Sent: Thursday, February 02, 2012 10:47 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Email vba code
I would try adding another block of code....
' Add my email as a CC recipient to the message.
Set objOutlookRecip = .Recipients.Add("myemail@mydomain")
objOutlookRecip.Type = olCC
Bob Peterson
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
ka0t1c_ang3l
Sent: Thursday, February 02, 2012 10:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Email vba code
I have setup my database to email out notices to vendors who have leases
expiring. It is also setup to send a CC email to my email address. If
I want to add another person to the CC list how would I add the email?
This is the code I'm using.
----------------
Dim db As DAO.Database, rst As DAO.Recordset
If IsNull(DLookup("[CONTRACT END DATE]", "[Expired Contracts]"))
Then
MsgBox "There are no contracts expiring."
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Main Menu"
Else
' Open a recordset on the expiring contracts
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [VENDOR E-MAIL], [CONTRACT
END DATE], [E-MAIL], [CONTACT PERSON] " & _
"FROM ([CONTRACT INFORMATION] INNER
JOIN [VENDOR INFORMATION] ON " & _
"[CONTRACT INFORMATION].[VENDOR ID] =
[VENDOR INFORMATION].[VENDOR ID]) " & _
"INNER JOIN [EMPLOYEE INPUT
INFORMATION] ON " & _
"[CONTRACT INFORMATION].[EMPLOYEE ID]
= [EMPLOYEE INPUT INFORMATION].[ID] " & _
"WHERE [CONTRACT END DATE] < #" &
(Date + Me.txtNotify) & "#")
' Loop through them all
Do Until rst.EOF
' Make sure we have a valid email
If Not IsNull(rst![VENDOR E-MAIL]) Then
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(rst![VENDOR E-MAIL])
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rst![E-MAIL])
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "Expire Date Approaching"
.Body = "ATTN:" & " " & rst![CONTACT PERSON] & " " & _
"-" & vbCrLf & vbCrLf & "Please be advised that your
contract with the Corpus Christi International Airport will expire on "
& _
rst![CONTRACT END DATE] & _
". Your attention to this matter is
greatly appreciated." & vbCrLf & vbCrLf & "Thank you." & vbCrLf & vbCrLf
& "CCIA Administration"
.Importance = olImportanceHigh 'High importance
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End If
' Get the next one
rst.MoveNext
Loop
' Close out
rst.Close
Set rst = Nothing
Set db = Nothing
End If
End Sub
--------
Could I just add the email address next to the rst![Email]?
Any help is appreciated!
Thanks!
Katrina
------------------------------------
Yahoo! Groups Links
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar