eddiepardon
asked on
Repetitive Task
Hello Experts,
I have a form that I use to email many reports to the same group of people. Currently I use:
Dim toMailRecipient, ccMailRecipient As String
Let toMailRecipient = "ManyEmails@Removed.com"
Let ccMailRecipient = "AFewEmails@Removed.com"
Each time that I need to update the email list I have to change in in many places.
I want to know a way to use one email list for toMailRecipient & one list for ccMailRecipient and reference it so that I only have to make one update.
Please be very detailed in your response.
I have a form that I use to email many reports to the same group of people. Currently I use:
Dim toMailRecipient, ccMailRecipient As String
Let toMailRecipient = "ManyEmails@Removed.com"
Let ccMailRecipient = "AFewEmails@Removed.com"
Each time that I need to update the email list I have to change in in many places.
I want to know a way to use one email list for toMailRecipient & one list for ccMailRecipient and reference it so that I only have to make one update.
Please be very detailed in your response.
ASKER
Jim,
The mail client is Outlook 2003.
The mail client is Outlook 2003.
I have examples of emailing but I only use CDO to get around the Outlook security which won't help you. I maintain a table of email addresses in Access or SQL Server; only one place to add, change or delete email addresses.
Jim
Jim
ASKER
Jim,
So if created a table say tblEmailAddress two fields say toEmailAddress & ccEmailAddress how could I reference that.
What would the code be? Also I would need to add the separator (;) between each address.
I have pasted the code I am currently using so that you can understand what I am doing.
Private Sub cmdSendEmail_Click()
'On Error Resume Next
Dim toMailRecipient, ccMailRecipient As String
Dim fso As New FileSystemObject
Dim fold As Folder
Dim f As File
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim dteToday As Date
dteToday = Date
Let toMailRecipient = "emails@removed.com"
Let ccMailRecipient = "emails@removed.com"
Set fold = fso.GetFolder("C:\Document
Set appOutLook = CreateObject("Outlook.Appl
Set MailOutLook = appOutLook.CreateItem(olMa
With MailOutLook
.To = toMailRecipient
.CC = ccMailRecipient
.Subject = "Blank Solution Tab in Remedy..."
.HTMLBody = "Package Managers,<br><br>Attached is a listing of all the Resolved/Closed" & _
" tickets so far for the current month that have blank solution tabs. Review these" & _
" and have the teams make the corrections in Remedy. Thanks!<br><br>"
For Each f In fold.Files
.Attachments.Add f.Path
Next
.Send
End With
Set fold = Nothing
Set fso = Nothing
Me.lblSolutionSent.Visible
Me.cmdMinimize.SetFocus
Me.cmdSendEmail.Visible = False
Me.cmdSendAllEmail.Visible
MsgBox "The Blank Solution Tab Report Has Been Emailed. "
End Sub
So if created a table say tblEmailAddress two fields say toEmailAddress & ccEmailAddress how could I reference that.
What would the code be? Also I would need to add the separator (;) between each address.
I have pasted the code I am currently using so that you can understand what I am doing.
Private Sub cmdSendEmail_Click()
'On Error Resume Next
Dim toMailRecipient, ccMailRecipient As String
Dim fso As New FileSystemObject
Dim fold As Folder
Dim f As File
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim dteToday As Date
dteToday = Date
Let toMailRecipient = "emails@removed.com"
Let ccMailRecipient = "emails@removed.com"
Set fold = fso.GetFolder("C:\Document
Set appOutLook = CreateObject("Outlook.Appl
Set MailOutLook = appOutLook.CreateItem(olMa
With MailOutLook
.To = toMailRecipient
.CC = ccMailRecipient
.Subject = "Blank Solution Tab in Remedy..."
.HTMLBody = "Package Managers,<br><br>Attached is a listing of all the Resolved/Closed" & _
" tickets so far for the current month that have blank solution tabs. Review these" & _
" and have the teams make the corrections in Remedy. Thanks!<br><br>"
For Each f In fold.Files
.Attachments.Add f.Path
Next
.Send
End With
Set fold = Nothing
Set fso = Nothing
Me.lblSolutionSent.Visible
Me.cmdMinimize.SetFocus
Me.cmdSendEmail.Visible = False
Me.cmdSendAllEmail.Visible
MsgBox "The Blank Solution Tab Report Has Been Emailed. "
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.
ASKER
Jim,
Works like a champ! Here is what I did based upon your suggestion:
Dim strTo As String
Dim strCC As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strTo = ""
strCC = ""
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT toEmailAddress FROM tblEmailAddress;")
rs.MoveFirst
Do While Not rs.EOF
strTo = strTo + rs!toEmailAddress
rs.MoveNext
Loop
toMailRecipient = strTo
Set rs = Nothing
Set db = Nothing
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ccEmailAddress FROM tblEmailAddress;")
rs.MoveFirst
Do While Not rs.EOF
strCC = strTo + rs!ccEmailAddress
rs.MoveNext
Loop
ccMailRecipient = strCC
Set rs = Nothing
Set db = Nothing
Works like a champ! Here is what I did based upon your suggestion:
Dim strTo As String
Dim strCC As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strTo = ""
strCC = ""
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT toEmailAddress FROM tblEmailAddress;")
rs.MoveFirst
Do While Not rs.EOF
strTo = strTo + rs!toEmailAddress
rs.MoveNext
Loop
toMailRecipient = strTo
Set rs = Nothing
Set db = Nothing
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ccEmailAddress FROM tblEmailAddress;")
rs.MoveFirst
Do While Not rs.EOF
strCC = strTo + rs!ccEmailAddress
rs.MoveNext
Loop
ccMailRecipient = strCC
Set rs = Nothing
Set db = Nothing
ASKER
Thanks for your help!
Hi eddie,
Glad that I could help you out.
Jim
Glad that I could help you out.
Jim
Jim