mvalencia2003
asked on
Mail Merge/EMail
How can I create 50 sheets with login information and email to 50 different people at once. Without having to email each one one by one ...
???
Thanks ---
???
Thanks ---
If each sheet has different login info, you do have to send separate emails. Here is an Access procedure that creates a filtered report and sends it as a PDF to multiple recipients.
Public Sub SendPDFEmails()
'Created by Helen Feddema 24-Jan-2010
'Last modified by Helen Feddema 24-Jan-2010
On Error GoTo ErrorHandler
Dim appOutlook As New Outlook.Application
Dim dbs As DAO.Database
Dim lngCount As Long
Dim lngEmployeeCount As Long
Dim lngID As Long
Dim msg As Outlook.MailItem
Dim rpt As Access.Report
Dim rstEmployees As DAO.Recordset
Dim strAttachmentsPath As String
Dim strBody As String
Dim strEmployeeName As String
Dim strEMailAddress As String
Dim strPrompt As String
Dim strQuery As String
Dim strRecordSource As String
Dim strReportFile As String
Dim strReportName As String
Dim strSQL As String
Dim strSubject As String
Dim strTitle As String
strAttachmentsPath = GetProperty("AttachmentsPath", "") & "\"
strSubject = GetProperty("MessageSubject", "Your custom report")
strBody = GetProperty("MessageBody", "Your current report is attached as a PDF")
strReportName = "rptEmployeeInvoices"
Set dbs = CurrentDb
Set rstEmployees = dbs.OpenRecordset("qryEMailEmployees")
lngEmployeeCount = rstEmployees.RecordCount
Debug.Print lngEmployeeCount & " employees need reports"
If lngEmployeeCount = 0 Then
strTitle = "No reports to send"
strPrompt = "No employees need reports; canceling"
MsgBox prompt:=strPrompt, _
buttons:=vbExclamation + vbOKOnly, _
Title:=strTitle
GoTo ErrorHandlerExit
End If
Do While Not rstEmployees.EOF
lngID = rstEmployees![EmployeeID]
strEmployeeName = rstEmployees![Salesperson]
strEMailAddress = rstEmployees![Email]
strReportFile = strAttachmentsPath & "Employee Invoices" _
& " for " & strEmployeeName & ".pdf"
Debug.Print "PDF save name and path: " & strReportFile
'Create filtered query as report record source
strRecordSource = "qryInvoices"
strQuery = "qryInvoicesPerEmployee"
If lngID <> 0 Then
strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
& "[EmployeeID] = " & lngID & ";"
End If
Debug.Print "SQL for " & strQuery & ": " & strSQL
lngCount = CreateAndTestQuery(strQuery, strSQL)
'Output customized report to PDF
DoCmd.OutputTo objecttype:=acOutputReport, _
objectname:=strReportName, _
outputformat:=acFormatPDF, _
outputfile:=strReportFile, _
autostart:=False
'Create new mail message and send to employee
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strEMailAddress
.Subject = strSubject
.Body = strBody
.Attachments.Add strReportFile
.Send
End With
NextEmployee:
rstEmployees.MoveNext
Loop
strTitle = "Done"
strPrompt = lngEmployeeCount & " PDFs created and emailed"
MsgBox prompt:=strPrompt, _
buttons:=vbInformation + vbOKOnly, _
Title:=strTitle
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in SendPDFEmails procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Public Function CreateAndTestQuery(strTestQuery As String, _
strTestSQL As String) As Long
'Created by Helen Feddema 7-28-2002
'Last modified 2-4-2003
On Error Resume Next
Dim qdf As DAO.QueryDef
'Delete old query
Set dbs = CurrentDb
dbs.QueryDefs.Delete strTestQuery
On Error GoTo ErrorHandler
'Create new query
Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
'Test whether there are any records
Set rst = dbs.OpenRecordset(strTestQuery)
With rst
.MoveFirst
.MoveLast
CreateAndTestQuery = .RecordCount
End With
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3021 Then
CreateAndTestQuery = 0
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function
If you want to do it with Outlook, take a look at the free Mail Merge with Attachments tool. It allows you to provide all needed data and automatically creates a separate persofified mail for each of the recipients.
DISCLAIMER: I recommend this tool because I'm one of the developers, so feel free to ask any further questions.
DISCLAIMER: I recommend this tool because I'm one of the developers, so feel free to ask any further questions.
If the same sheet goes to everyone, you could make a distribution list and use that as the To address for a single email.
ASKER
the mail merge using MS Word creates a login page for each but they are all in the same file .. would like to get pretty much 1 seperate file for sheet (page)
...
...
Is the login sheet the same for all recipients, or different for each one? If they are different, where is the different data stored?
ASKER
different for each ,
data is in an excel file now ...
data is in an excel file now ...
This is exactly what recommended above tool does. It processes you spreadsheet (saved as CSV from Excel) and creates a separate message for each table row using the provided data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,
Open Word. From the Mailings tab, choose the 'Start Mail Merge' button and then 'Step by Step Mail Merge Wizard'.
If you still have problems, then let us know here