ACCESS FEED: Re: [MS_AccessPros] Email vba code

Kamis, 02 Februari 2012

Re: [MS_AccessPros] Email vba code

Hi Kat,

you can also do something like this:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function MailParameters()
'originally written by Ricky Hicks, modified by Crystal
   Dim outApp As Outlook.Application, outMsg As MailItem
   Set outApp = CreateObject("Outlook.Application")
   Set outMsg = outApp.CreateItem(olMailItem)
   With outMsg
      '.Importance = olImportanceHigh
      .To = Me.eMailAddress 'or whatever
      '.CC = "CC EMAIL ADDRESS GOES HERE"
      '.BCC = "BCC EMAIL ADDRESS GOES HERE"
'      .Subject = "YOUR SUBJECT GOES HERE"
'      .Body = "YOUR_E-MAIL_MESSAGE_GOES_HERE"
      '.Attachments.Add "YOUR FILE PATH AND NAME", , , "YOUR FILES NAME"
      ' If you want the screen to be seen then add the following line
      .Display
      '.Send
   End With

   Set outApp = Nothing
   Set outMsg = Nothing

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I believe you can use a comma delimited list for the recipients

Warm Regards,
Crystal

Microsoft MVP
remote programming and training

Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

*
   (: have an awesome day :)
*

________________________________
From: Robert Peterson

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

[Non-text portions of this message have been removed]