Hi,
I currently have a code set up which sends an attachment from a drive on my computer to all the email addresses in a certain query. I would like to change the code so that I can add a button that will allow me to send an email with a specific attachment depending on their individual code in a table, e.g each person will receive a specific attachment. At the moment, the attachments are saved with the same file name as the person's specific code.
The code I am currently using is:
Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("People")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
Set rsEmail = qdf.OpenRecordset()
Set oLook = CreateObject("Outlook.Application")
With rsEmail
.MoveFirst
Rem Do Until rsEmail.EOF
myRecipient = .Fields(3)
If IsNull(myRecipient) = False Then
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.to = myRecipient
.body = "See attached"
.Subject = "Test Email"
.Attachments.Add ("K:\DatabaseTest\buildingreport.docx")
.Send
End With
End If
.MoveNext
Rem Loop
End With
Set oMail = Nothing
Set oLook = Nothing
End Sub
I currently have a code set up which sends an attachment from a drive on my computer to all the email addresses in a certain query. I would like to change the code so that I can add a button that will allow me to send an email with a specific attachment depending on their individual code in a table, e.g each person will receive a specific attachment. At the moment, the attachments are saved with the same file name as the person's specific code.
The code I am currently using is:
Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("People")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
Set rsEmail = qdf.OpenRecordset()
Set oLook = CreateObject("Outlook.Application")
With rsEmail
.MoveFirst
Rem Do Until rsEmail.EOF
myRecipient = .Fields(3)
If IsNull(myRecipient) = False Then
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
.to = myRecipient
.body = "See attached"
.Subject = "Test Email"
.Attachments.Add ("K:\DatabaseTest\buildingreport.docx")
.Send
End With
End If
.MoveNext
Rem Loop
End With
Set oMail = Nothing
Set oLook = Nothing
End Sub
Last edited: