[johnsong3]

I have the code written and I feel like it works well (I am a beginner so any suggestions to improve the code would be greatly appreciated). I am working on a project that will send emails using the information found in a query. Now that I have the code working so it will actually create the correct email with all of the intended attachments and etc. I need to add a piece of code to the end to keep track of the emails sent in another table. Here is the code that I am using...

---------

Option Compare Database
Public Function SendMail()
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 BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim Invoice As String
Dim A1013R As String
Dim A2016R As String
Dim Azip As String
Dim FolderName As String


Set db = CurrentDb()
Set MailList = db.OpenRecordset("CycleDistribution")
Set fso = New FileSystemObject

Set MyOutlook = New Outlook.Application
MyInput = InputBox("Please enter the Billing Range", _
"Biller", "Input the period range in MM/DD/YYYY - MM/DD/YYYY format")
If MyInput = "Input the period range in MM/DD/YYYY - MM/DD/YYYY format" Then
MsgBox "You have entered an incorrect Range. Please try again!"
End If
MsgBox "Emails for the " & MyInput & " billing period will now be sent!"
Set Fld = CreateObject("Shell.Application").BrowseForFolder(0, "Select Back-Up Folder", 512)
If Not Fld Is Nothing Then
FolderName = Fld.Self.Path
If Right(FolderName, 1) <> "\" Then
FolderName = FolderName & "\"
End If
End If

Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList("email") & ";" & MailList("EmailAdd2") & ";" & MailList("EmailAdd3") & ";" & MailList("EmailAdd4") & ";" & MailList("EmailAdd5") & ";" & MailList("EmailAdd6") & ";" & MailList("EmailAdd7")
Invoice$ = FolderName & MailList("InvoiceFN")
A1013R$ = FolderName & MailList("1013RFN")
A2016R$ = FolderName & MailList("2016RFN")
Azip$ = FolderName & MailList("ZipFN")
Subjectline$ = MailList("Subjectln") & " - " & MailList("ClientName")
If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If
If MailList("EmailBody") = "Zip-File" Then
MyBodyText$ = "Attached please find a password protected zip file for the period " & MyInput & ". "
End If
MyMail.Subject = Subjectline$
MyMail.Body = MyBodyText$

MyMail.Attachments.Add Invoice$, olByValue, 1, ""
If Not MailList("1013RFN") = "" Then
MyMail.Attachments.Add A1013R$, olByValue, 1, ""
End If
If Not MailList("2016RFN") = "" Then
MyMail.Attachments.Add A2016R$, olByValue, 1, ""
End If
If Not MailList("ZipFN") = "" Then
MyMail.Attachments.Add Azip$, olByValue, 1, ""
End If

'MyMail.Send

MyMail.Display

MailList.MoveNext
Loop

Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function

--------

I appreciate any and all help!
Thank you

[OBP]

That does not look like any Beginner's code I have ever seen, it is very good.
What you haven't explained is the name of the table and it's Field names, with the data thta you want to save.

[johnsong3]

Well I have taken bits and pieces of the code from various places and I have done some research to make it fit my needs. I would like to save information coming from the "CycleDistribution" query. Ideally, I would like to capture all of the information found on that table and also include an exact(or as close as we can possibly get) time it was sent. The fields in the "CycleDistribution" query data are --> Distribute(yes or no), Cycle, ClientName, EmailBody, InvoiceFN, 1013RFN, 2016RFN, ZipFN, Email, EmailAdd3, EmailAdd4, EmailAdd5, EmailAdd6, EmailAdd7 and Subjectln . I need to be able to determine how long it has taken for the emails to be sent and also at what time each email was sent.( I don't even know if this would be possible.)

-Thanks!

[OBP]

What do yuo mean by "I need to be able to determine how long it has taken for the emails to be sent"?
How long Outlook takes to send them?
The time that the data was sent to Outlook is easy enough to do.

[johnsong3]

If it is possible it would be great to get that information from Outlook(as I think that would be the most accurate time)

[OBP]

Well I don't know how to do that part, but I can help you with the rest if you like, do you need to store all the data from the "CycleDistribution" query or just the ID fields from is?

[johnsong3]

OBP,

All of the fields from CycleDistribution would be great!

Thanks,
Johnsong3

[OBP]

What is the name of the table that you want the data put in?
You already have the CycleDistribution recordset open so it is just a case of adding the data to the Table.

[johnsong3]

CycleRec is the table name

[OBP]

OK, add this line to your Dim statements

Dim rsTable as object

After this line "Set fso = New FileSystemObject" add

Set rsTable = db.OpenRecordset("CycleRec")

Before this line "MailList.MoveNext" add

With rstable
.AddNew
!senttoOutlookat = Now()
!cycle = MailList.cycle
!ClientName = MailList.ClientName ' you can add the other fields after this
.Update
.Bookmark = .LastModified
End With

After the Loop add
Set rstable = Nothing

[johnsong3]

OBP,

That works like a charm! Thank you very much!

Johnsong3

[OBP]

May pleasure, can you mark the thread solved if it is.
If you should find something on Outlook times perhaps you could post it on here.
I have a couple of links for you to look at if you want?
I can post them tomorrow.