How can I get my emails generated from MS Access to automatically send

[tstrike]

I can get the Emails to gerenate as I want them, but in order to send them, I need to go to each email and manually send it. I know in the past I was able to send with our seeing the emails, but that was awhile ago. I am using MS Access 2003 amd Lotus notes 8.5.1

Here is the code I am using:

strSQL = "Select * " _
& "From tblBankAccountNumbers " _
& "Where (((tblBankAccountNumbers.BankName)=""Wells Fargo"") AND " _
& "((tblBankAccountNumbers.Contacts) Is Not Null))"
rst.Open strSQL
'Opens record set containing data existing in both the Text file and the Payroll Bank Data table
Do Until rst.EOF
CurrentDb.QueryDefs.Delete "qryDailyWellsData"
strSQL = "Select [As-Of Date], [As-Of-Time], [Bank ID], [Bank Name], State, [Acct No], [Acct Type], [Acct Name], " _
& "Currency, IBN, [BAI Type Code], [Tran Desc], [Debit Amt], [Credit Amt], [0 Day Flt Amt], [1 Day Flt Amt], " _
& "[2+ Day Flt Amt], [Customer Ref No], [Value Date], Location, [Bank Reference], [Tran Status], " _
& "[DescriptiveText1] & [DescriptiveText2] & [DescriptiveText3] & [DescriptiveText4] & [DescriptiveText5] & " _
& "[DescriptiveText6] & [DescriptiveText7] AS DescriptiveText " _
& "FROM txtWells " _
& "WHERE (((txtWells.[Acct No])='" & rst("BankAccount#") & "'))"
Set QryDef = CurrentDb.CreateQueryDef("qryDailyWellsData", strSQL)
'Delete and recreate query for account
ETo = rst("Contacts")
Subject = "Wells Bank prior day data for " & Format(Date, "mm/dd/yyyy")
Msg = "Here is your prior day bank data for Account " & rst("BankAccount#") & ". Please use " _
& "this date instead of pulling the data from Wells Fargo CEO. Please let me know if this is helpful. " _
& "Thank you"
DoCmd.SendObject acSendQuery, "qryDailyWellsData", acFormatXLS, ETo, , , Subject, Msg, False
rst.MoveNext
Loop

 

[tstrike]

Thank you for any help or direction you can give me.

Trevor

 

[BallunarCrew]

Here is the basic coding for the way I send emails. Towards the bottom, you can have it either .Display to display the message (my customers like to be able to review/edit the emails before they go out) or use .Send and it will go automatically. It is a different way of doing it since I use the Outlook object but it works fine. Be sure to check the Outlook Object Library in the Tools-References menu option in the code window if you use the Outlook object. I first created this code in Access 95 and continue to use it today in 2007.

' Create the Outlook session
Set objOutlook = CreateObject("Outlook.Application")
' Create the message
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' ' Add the To recipeitn(s) to the message
Set objOutlookRecip = .Recipients.Add(Me!Emailname)
objOutlookRecip.Type = olTo

' Set the Subject and Body of the message
.Subject = DocSubject
.Body = Me!txtMessage
' Resolve each Recipient's name
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
'If chkEditMessage Then
.Display
'Else
' .Save
' .Send
' End If
End With
Set objOutlook = Nothing

 

[tstrike]

Thank you for your help.

My goal is to send through lotus notes without having to view the email. I also do not want to have to save the object I am sending to a drive. The sendobject command will let me do that, but does not actually send the email. At this point, everything works accept the fact that I need to actually hit the send button for each email that is set up.

Again, thank you for your help.

Trevor

 

[jambai]

tstrike,

Insert the send mail procedure into AutoExec Macro and let the routine runs by timer event. The Access needs to be opened for this.

I would create a service to do this.

Thanks
Jambai

 

[tstrike]

Jambai,

Thank you for your help, not sure how putting this in an AutoExec macro would change the fact that the emails are created, but not sent. It does not take long to hit the send button on them all, but would rather it be seemless.

Thank you for your advice

Trevor