Lisa Norton
asked on
Email Loop ms access Database
Unit-Checkv1.mdbGood Afternoon;
I am working on a database that will email unit checks to customers. I have a query that will show the email addresses of our customers based on the selection made on the unbound form for the company name. I also have in column 3 of the employee drop down contains their email address. When the user selects their name, the email address populates in a text box (for test purposes only), however when they drop down the company name I should get several email addresses based on the number of people each company would like notified on the unit check. The email piece works fine, I just need to find another way to get the other emails to populate with the employees email address as email; email; email; and so on. I have tried Dlookup and only get one email address for the company and not the rest. I may need a loop but i will need help with that.
me.txtemailaddy is a text box on the main unobound form. This is being pulled from a combo box cbo.Emp column3 (where email for the employee is stored)
I have a query Q_ContactEmail that returns the correct email addresses based on the cboCompany combo box.
I would like to have me.txtemailaddy and Q_contactEmail.[emailaddy] to be concactenated into multiple email addresses in the to: line of the email.
I hope this makes sense.
Thank you,
Lisa
The email code is as follows:
Private Sub Command67_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
'DoCmd.OutputTo acOutputReport, "r_qrlvetknbyee", acFormatPDF, "C:\Users\13618\Desktop\PD Fs\Stateme nts\ " & Me.Text32 & ".pdf", True
'Export report in same folder as db with date stamp
todayDate = Format(Date, "YYMMDD")
fileName = "C:\users\13618\desktop\PD Fs\" & Me.TxtFileName & ".pdf"
'Application.CurrentProjec t.Path & "\" & Me.Text32 & ".pdf"
'*** below rem out for test
'Leave Report_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "r_UnitCheckByEmp", acFormatPDF, fileName, True
'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem )
With oEmail
.Recipients.Add (Me.TxtEmailAddy)
.Subject = "Unit Check"
.Body = "Please see attached Unit Check"
.Attachments.Add fileName
.Send
End With
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub
I am working on a database that will email unit checks to customers. I have a query that will show the email addresses of our customers based on the selection made on the unbound form for the company name. I also have in column 3 of the employee drop down contains their email address. When the user selects their name, the email address populates in a text box (for test purposes only), however when they drop down the company name I should get several email addresses based on the number of people each company would like notified on the unit check. The email piece works fine, I just need to find another way to get the other emails to populate with the employees email address as email; email; email; and so on. I have tried Dlookup and only get one email address for the company and not the rest. I may need a loop but i will need help with that.
me.txtemailaddy is a text box on the main unobound form. This is being pulled from a combo box cbo.Emp column3 (where email for the employee is stored)
I have a query Q_ContactEmail that returns the correct email addresses based on the cboCompany combo box.
I would like to have me.txtemailaddy and Q_contactEmail.[emailaddy]
I hope this makes sense.
Thank you,
Lisa
The email code is as follows:
Private Sub Command67_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
'DoCmd.OutputTo acOutputReport, "r_qrlvetknbyee", acFormatPDF, "C:\Users\13618\Desktop\PD
'Export report in same folder as db with date stamp
todayDate = Format(Date, "YYMMDD")
fileName = "C:\users\13618\desktop\PD
'Application.CurrentProjec
'*** below rem out for test
'Leave Report_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "r_UnitCheckByEmp", acFormatPDF, fileName, True
'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem
With oEmail
.Recipients.Add (Me.TxtEmailAddy)
.Subject = "Unit Check"
.Body = "Please see attached Unit Check"
.Attachments.Add fileName
.Send
End With
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub
You could do something along the lines of
and call it in your existing code by doing
Don't forget
Public Function GetEmails() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
On Error GoTo Error_Handler
Set db = CurrentDb()
sSQL = "SELECT emailaddy FROM Q_contactEmail;"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
Do While Not .EOF
GetEmails = GetEmails & ![emailaddy] & ";"
.MoveNext
Loop
End If
End With
Error_Handler_Exit:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
If Not db Is Nothing Then Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetEmails" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
and call it in your existing code by doing
Private Sub Command67_Click()
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String
Dim todayDate As String
On Error GoTo Error_Handler
'DoCmd.OutputTo acOutputReport, "r_qrlvetknbyee", acFormatPDF, "C:\Users\13618\Desktop\PDFs\Statements\ " & Me.Text32 & ".pdf", True
'Export report in same folder as db with date stamp
todayDate = Format(Date, "YYMMDD")
fileName = "C:\users\13618\desktop\PDFs\" & Me.TxtFileName & ".pdf"
'Application.CurrentProject.Path & "\" & Me.Text32 & ".pdf"
'*** below rem out for test
'Leave Report_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "r_UnitCheckByEmp", acFormatPDF, fileName, True
'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
.Recipients.Add (Me.TxtEmailAddy & ";" & GetEmails())
.Subject = "Unit Check"
.Body = "Please see attached Unit Check"
.Attachments.Add fileName
.Send
End With
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
Error_Handler_Exit:
On Error Resume Next
If Not oEmail Is Nothing Then Set oEmail = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Command67_Click" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
Don't forget
- Error handling
- Give your controls meaningful names instead of the default numeric names which mean nothing
- Clean up your variables, anything you set you should set to Nothing at the end of your code.
ASKER
Dan,
Its going to be awhile before I can get back to you. I created a module and pasted the code into it and I have a hot mess. my drop downs are not working. I've remarked the code out and still have error after error. I'm glad I backed up before I started this.
Its going to be awhile before I can get back to you. I created a module and pasted the code into it and I have a hot mess. my drop downs are not working. I've remarked the code out and still have error after error. I'm glad I backed up before I started this.
ASKER
I get a compile error on this line:
.Recipients.Add (Me.TxtEmailAddy & ";" & GetEmails())
Compile error: expected variable or procedure not module.
.Recipients.Add (Me.TxtEmailAddy & ";" & GetEmails())
Compile error: expected variable or procedure not module.
ASKER
I think I have the compile error straightened out. I did some searches and renamed the module modGetEmails. I am not sure if that is correct or not.
I am getting a run time error 3061Too few parameters expected 1 and
an error Outlook does not recognize one or more names.
Any help would be greatly appreciated.
I am getting a run time error 3061Too few parameters expected 1 and
an error Outlook does not recognize one or more names.
Any help would be greatly appreciated.
Does your query expect parameters?
ASKER
yes from an unbound form F_MainSB
and I am still getting the compile error on this line .Recipients.Add (Me.TxtEmailAddy & ";" & GetEmails())
Is there a way to tell what parameter its looking for? The error says source: GetEmails
and I am still getting the compile error on this line .Recipients.Add (Me.TxtEmailAddy & ";" & GetEmails())
Is there a way to tell what parameter its looking for? The error says source: GetEmails
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I am getting error 3265 Item not found in this collection on Line 40 and
Outlook does not recognize one or more names on line 110
Outlook does not recognize one or more names on line 110
Q_contactEmail is the query's name?
Also, I posted the function and modified it a minute or so later. Please be sure that sSQL = "Q_contactEmail"
Also, I posted the function and modified it a minute or so later. Please be sure that sSQL = "Q_contactEmail"
ASKER
yes, Q_ContactEmail is the query's name
ASKER
I'm still getting the same 2 errors.
Any chance you can post the db for review?
ASKER
It's uploaded under my initial post
See the attached.
Also, since you are using a query to get all the contact emails for the recipients, I do not believe Me.TxtEmailAddy is necessary anymore and thus
Don't forget to use Option Explicit in all your modules, include error handling throughout your code and debug your code often to avoid issues.
Unit-Checkv1.zip
Also, since you are using a query to get all the contact emails for the recipients, I do not believe Me.TxtEmailAddy is necessary anymore and thus
.Recipients.Add (Me.TxtEmailAddy & ";" & GetEmails())
can simply be replaced with.Recipients.Add (GetEmails())
Don't forget to use Option Explicit in all your modules, include error handling throughout your code and debug your code often to avoid issues.
Unit-Checkv1.zip
ASKER
I am still getting the error message that outlook does not recognize one or more names.
It worked for me? I'd be checking the e-mail addresses to ensure they are valid.
ASKER
i have entered validation rules in the table, and I have deleted the email addresses and re-entered them and still get the error. This is rather frustrating.
For testing purposes, what happens if you switch .Send to .Display? Does the e-mail generate properly or do you still get an error?
Instead of using your e-mail code, try http://www.devhut.net/2010 /09/03/vba -send-html -emails-us ing-outloo k-automati on/ to see if the issue is with you e-mail code itself (I suspect Outlook can't resolve one of the addresses).
Instead of using your e-mail code, try http://www.devhut.net/2010
ASKER
changing to .display works. The email generates properly and the attachment is there.
To me that indicates an issue with the recipients, and you need to add a loop mechanism similar to the one in the function I provided
For Each oOutlookRecip In .Recipients
If Not oOutlookRecip.Resolve Then
oOutlookMsg.Display
End If
Next
to loop through them one by one and validate and report back if necessary.
ASKER
It appears that my email address is what is hanging this up, I do have a work around for it.
Thank you so much for your help
Thank you so much for your help
ASKER
Thank you very much!
If your source is an array of strings, you can use the VBA "Join" function to get a comma-delimited string.
I'm sure there some folks out there eager to provide examples.