Automated Email via Access

[hvns]

Hello,

I have read some of the threads already with this similar topic but it does not seem to fit my needs. I am wanting to do somewhat of a mass email(s).

Here is what I basically want to do:
Send an email to a contact from a table that contains their information, with the list of back orders that they currently have that is produced from a query. I have a query that contains a list of the contacts with their back orders - the attached example only shows 1 vendor but the query out put will have many. I only want a vendor to receive their late orders / not everyones.

So there would be multiple emails sent out. I would prefer the late order information in the body of the email. I am not sure if I have given enough information on what I would like done but would answer any questions to give more detail.

If there is a post you know of that does this that link would be apprecaited as well. I have never attempted this before so I am very green at it and your patience is appreciated.

 

[Bandenjamin]

Change the query to only be on the one vendor. Create a report from that query and use DoCmd.SendObject to create the email.

--Dan
Whenever you find yourself on the side of the majority, it is time to pause and reflect.
Mark Twain

 

[ck1999]

To put the information in the body of the email you could use

Sub sendmass()
    
    Dim dbs As Database, tdf As TableDef, rstpeople As Recordset, rstemail As Recordset, rstserver As Recordset
    
    Const cdoSendUsingMethod = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing"[/URL]
    Const cdoSendUsingPort = 2
    Const cdoSMTPServer = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver"[/URL]
    Const cdoSMTPServerPort = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport"[/URL]
    Const cdoSMTPConnectionTimeout = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"[/URL]
    Const cdoSMTPAuthenticate = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"[/URL]
    Const cdoBasic = 1
    Const cdoSendUserName = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername"[/URL]
    Const cdoSendPassword = "[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword"[/URL]

    
    'Return reference to current database.
    Set dbs = CurrentDb
    
    'define recordsets
    Set rstemail = dbs.OpenRecordset("tblemail")
    Set rstpeople = dbs.OpenRecordset("tblpeople")
    Set rstserver = dbs.OpenRecordset("tblserverinfo")
    
    rstpeople.MoveFirst
    
    Do While Not rstpeople.EOF
        If rstpeople("sendemails") = True Then
            Set objMessage = CreateObject("CDO.Message")
            objMessage.Subject = rstemail("subject")
            objMessage.Sender = rstserver("emailaddress")
            objMessage.To = rstpeople("email")
            objMessage.TextBody = rstpeople("fname") & "," & vbCrLf & vbCrLf & rstemail("body") & vbCrLf & vbCrLf & vbCrLf & rstemail("footer")
        
    
            ' Set config fields we care about
            With objMessage.Configuration.Fields
                .Item(cdoSendUsingMethod) = 2
                .Item(cdoSMTPServer) = rstserver("smtpserver")
                .Item(cdoSMTPServerPort) = rptserver("portnumber")
                .Item(cdoSendUserName) = rstserver("username")
                .Item(cdoSendPassword) = rstserver("password")
                .Update
            End With
              
        
           
                objMessage.send
                MsgBox "emails sent"
            End If
        rstpeople.MoveNext
    Loop
    
    dbs.Close
    Set dbs = Nothing

End Sub

I use a table for your email server info
I use 1 table for the body of the email to make a template kind of (You could do the same or leave it off)
3rd table is email address.

This will loop through the tblpeople and send each an email

You could have it go through or query and do the same.

This is a guideline and and idea of how I do it this is not exact code for what you requested.

ck1999