Hi all,
I modified a database used for sending multiple emails to a fixed group of recipients. This works but I feel I am using a convoluted method with spaghetti code.
In my example, I have three files and three recipients -I want to send them to-each recipient only receiving their own file.
My database has 3 tables (TbEmails1, 2 and 3), 3 queries QryAddress1, 2 and 3 query each table for the email address and 3 further queries-QrySource1, 2 and 3.
QryAddress1 SQL;
Code:
SELECT TbEmails1.EMailAddress AS EMail
FROM TbEmails1;
QrySource1 SQL:
Code:
SELECT *
FROM Tbemails1;
I have a form with a button that fires a macro:
Code:
Private Sub Email_Click()
stDocName = "SendMail"
DoCmd.RunMacro stDocName
MsgBox "Files Emailed", vbInformation, "Complete"
End Sub
This macro launches 3 functions-EmailMerger1, 2 and 3. EmailMerger1 code:
Code:
Public Function SendEMail1()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim fso As FileSystemObject
Dim stDocName As String
Set fso = New FileSystemObject
Subjectline$ = "Charles"
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset("QryAddress1")
Dim MyRecip As Outlook.Recipient
Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
Set MyRecip = MyMail.Recipients.Add(MailList("email"))
MyRecip.Type = olTo
MyMail.Subject = Subjectline$
MyMail.Attachments.Add "C:\My documents\Charles.XLS", olByValue, 1, "My Displayname"
Dim MyQuery As QueryDef
Set MyQuery = CurrentDb.QueryDefs("QrySource1")
MyQuery.SQL = "select * from Tbemails1 "
MyQuery.Close
MyMail.Send
MailList.MoveNext
Loop
Set MyMail = Nothing
MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
Effectively, I can send an existing unique file to an individual for every set of TbEmailX, QryAddressX, QrySourceX and EmailMergerX function.
I’m trying to simplify it to run from a single tables and queries using a loop, where a table TbEmailsAll containing the filename, recipient and email address using subsequent Queries joined to TbEmailsAll in the query e.g. QryAddressAll, QrySourceAll and EmailMergerAll. Would attach, but even stripped and zipped, its 2.4Mb-just over the limit.
If anyone can advise/point me in the right direction, I would be very interested.
Many thanks,
Mattbro451