Emailing With Attachments

[Sprinks]

My company estimates construction projects for architects at an early stage
of design. I have a mini-application that assists us in preparing cover
letters to send to vendors requesting a quotation for items specified in the
construction documents.

The main tables are:

tblCompany
-----------------
CompanyID AutoNumber PK
CompanyName Text
....
CompanyType Integer FK->CompanyType
SubType Integer FK->SubType
RepID Integer FK->tblCompany
.....
Email Text

tblRFQBySchedule
---------------------
JobNum Text FK->Projects
DueDate Date/Time

tblRFQByScheduleItems
----------------------------
ItemID AutoNumber PK
JobNum Text FK->tblRFQBySchedule
MfrID Integer FK->tblCompany
EquipDesc Text

The user enters the project number on a main form based on tblRFQBySchedule,
and then the items on a continuous subform based on tblRFQByScheduleItems,
linked by the JobNum. As the RepID for each manufacturing company is stored
in tblCompany, it provides the means to produce a report, one page for each
rep, requesting they quote items for one or more manufacturer's items.

Originally, we used this as a fax cover sheet, and attached the portion of
the equipment schedule that applied to them, laboriously copying the section
from a 40" x 30" drawing.

The current process publishes the report to MS Word, and each page is pasted
to an email, and a .pdf version of the schedule, located in a folder named by
the JobNum, is attached. This is a quantum leap from where we were, but we'd
like to automate it more fully.

My intent is to add an ItemAttachment table, a detail table (many side) for
tblRFQByScheduleItems:

ItemAttachment
---------------------------
ItemAttachmentID AutoNumber PK
ItemID Integer FK to RFQByScheduleItems
FileSpec Text

Ideally, the user would enter the project and related items as before, but
for each item would add one or more attachment files. When the user pressed
the "Send Emails" button, it would loop through the records, and create an
email for each rep, with the report text placed in the body, and the attached
files as attachments. It would pause on each record to allow the user to
make any necessary customizations or corrections, until the user pressed Send.

I need help with:
- Presenting the file list in a combo box for selection
- Creating the email
- Pausing the application to allow the user to confirm each email

We are using MS Outlook 2003 SP2 & Access 2003 under Windows XP Professional
2002 Service Pack 2.

Thanks for any advice.
Sprinks

 

[Surendran]

--I need help with:
- Presenting the file list in a combo box for selection
- Creating the email
- Pausing the application to allow the user to confirm each email

- Presenting the file list in a combo box for selection
Try this:

Since you want to select multiple files, use a list box instead of
combo box as in a list box you can set the property "Multi select"
to Simple or extended by which multiple items can be selected but in
a combo box only one item can be selected.

Now how to get the files and fill the list box ?

For this follow the steps given below.

1.Cretae a new table with two fields.
say table name - tblFileNames
Fields
FileName - Text
JobNum - Number
2. Create a list box in your main form
Name : lstFileName
Multi Select : Simple
RowsourceType:Table/Query
Rowsource:"SELECT tblFileNames.JobNum, tblFileNames.FileName
FROM tblFileNames;"
Bound Column : 2
Column Widths: 0";1"

3.Go to VBA editor. Set reference to "Microsoft Scripting Runtime"
library under Tools/References

4.In the Main form module enter the following procedure.
In the code replace the file path with actual file path.

Private Sub GetFileList()
Dim fso As New FileSystemObject

Dim f As File
Dim folFilesPath As Folder

Dim strFilesPath As String

'Replace the directory path as appropriate
strFilesPath = "C:\FilesForAttachment"

Set folFilesPath = fso.GetFolder(strFilesPath)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

rst.Open "tblFileNames", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

Dim strSQL as String
strSQL = "DELETE tblFileName.* FROM tblFileName;"

'Before getting files, delete old entries

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Get all files from the file path folder
For Each f In folFilesPath.Files
rst.AddNew
rst!filename = f.Name
'Try to extract JobNum in the File name
'If possible assign it.Comments given below.
'rst!JobNum = JobNum
rst.Update

Next

Set rst = Nothing

End Sub

5. In the open event of main form enter the one line code given below
i.e. you are calling the above procedure

getFileList

Save and close the form.

'Now open your form

If you have some files in the File Path given, then you
should be able to see the file names in the list box after
the form is open.

Now you may think why "JobNum" field is included in the table ?

You have mentioned that the file name of Schedule files
to be attached is in someway mapped to JobNum.

If you can extract the JobNum part in the file name using
some String handling functions, then while adding file names
in the table,you can also add related JobNum along with file name.

By this it is possible to show only related files for a Jobnum in
the list box when you move from one JobNum record to another.
For this you can write code to filter the list
in the Current Event of form.

Otherwise the list box will show all files for all Job Numbers.
========================================================
Now you can select files in the list box for attachment.

Now the issue is you want to select file names from the list box
which is in Main Form but the ItemID is in the sub form.

So before selecting files from the list box, you should make
the ItemId in the sub form for which files to be selected
as the current record.

Now assume for current ItemId you have selected some files.
Then Where and how to store the file names selected ?

You have suggested an ItemAttachment table which is correct.
Only a small alteration is required.

This table is to be used for storing file names for attachment
only tempororily till the mail is sent.This is my suggestion.

So create table with only two fields.
tblItemAttachment
--------------------------------------------------------
ItemID Integer
FileName Text

To add the file list in the above table, create a button
in the main form say cmdItemFileList(button name) and
caption "Add Item files".

In the On Click event of the button enter the following code.

Private Sub cmdItemFileList_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim intItemID as Integer

Dim varNumber as Variant


'Replace with actual table name in your code
rst.Open "tblItemAttachment", cnn, adOpenKeyset, adLockOptimistic,
adCmdTable

intItemId = Me.frmSub.Form!ItemId
'Delete old entries if any for the ItemID
Dim strSQL as String
strSQL = "DELETE tblItemAttachment.* FROM tblItemAttachment " & _
"WHERE (((tblItemAttachment.ItemID)=" & intItemID & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'Now add file names for attachment

For Each varNumber in lstfileName.ItemsSelected
rst.addNew
rst!ItemId = intItemId
rst!FileName = lstFileName.Itemdata(varNumber)
rst.update
Next

set rst = Nothing

End Sub

For each ItemId select files and click "Add item Files" button.

Before selecting files in the list for new ItemId you can deselect
the old selections by providing a button near list box and add code
like this.
-------------------------------------------------------------------------------------
'For next ItemID, refresh the list box for fresh selection.
Private Sub cmdDeselectAll_Click()
Me.lstFileName.RowSourceType = "table/query"
Me.lstFileName.RowSource = "SELECT tblFileName.JobNum,
tblFileName.FileName " _
& "FROM tblFileName ORDER BY [FileName];"

Me.lstFileName.Requery

End Sub
-------------------------------------------------------------------------------------
Having entered file names for all the ItemId in the sub form
now you can send mail for current JobNum.

- Creating the email
- Pausing the application to allow the user to confirm each email

For creating the mail the following data is required.

Recipient Mail Address
Message subject
Message Body
Message Attachments

If you call the procedure given below in the On Click
Event property of Send mail button in your form,
Outlook will open for each ItemId with all the particulars
supplied

Private Sub SendMessage()
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strFilePath As String
Dim strFilePathAndName As String
Dim strSubject As String
Dim strBodyMessage As String

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

'Replace with actual path.
strFilePath = "C:\FilesForAttachment\"

'Create the Outlook session
Set objOutlook = CreateObject("Outlook.Application")

'Create the message
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


Dim rstFile As ADODB.Recordset
Set rstFile = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

'Replace with actual table name in your code
rstFile.Open "tblItemAttachment", cnn, adOpenKeyset, adLockOptimistic,
adCmdTable

'For looping all itemId's in the sub form create a recordset
Dim rst As DAO.Recordset
Set rst = Me.frmSub.Form.RecordsetClone

'For each itemId, open outlook with Mail,subject
'Body and Attachments and display to send
Do Until rst.EOF
rst.MoveFirst

With objOutlookMsg
'Add the To recipient(s) to the message

strTo = DLookup("EMail", "tblCompany", "CompanyID = " &
Me.frmSub.Form!MfrID)

Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = olTo

'Add the CC reipient(s) to the message
'Set objOutlookRecip = .Recipients.Add(strCc)
'objOutlookRecip.Type = olCC

'Add the BCC recipient(s) to the message
'Set objOutlookRecip = .Recipients.Add(strBcc)
'objOutlookRecip.Type = olBCC

'Set the Subject, Body and Importance of the message
'For strBody assign with string data if any from the form
'or other means
.Subject = strSubject
.Body = strBodyMessage
.Importance = olImportanceNormal

'Add attachments to the message.
'Find the attachment files using rstFile
'Then attach with the message
'If recordset is already filtered remove it.
rstFile.Filter = adFilterNone
'Filter for current ItemID
rstFile.Filter = "ItemId = " & rst!ItemId
Do Until rstFile.EOF
strFilePathAndName = strFilePath & rst!FileName
Set objOutlookAttach = .Attachments.Add(strFilePathAndName)
Loop
'This code will open outlook and pause
objOutlookMsg.Display

'To send msg directly use the code as shown.
'.Send
End With
rst.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

 

[Sprinks]

Thank you, Surendran. I look forward to implementing this.

Best regards.
Sprinks

--I need help with:
- Presenting the file list in a combo box for selection
- Creating the email
- Pausing the application to allow the user to confirm each email

- Presenting the file list in a combo box for selection
Try this:

Since you want to select multiple files, use a list box instead of
combo box as in a list box you can set the property "Multi select"
to Simple or extended by which multiple items can be selected but in
a combo box only one item can be selected.

Now how to get the files and fill the list box ?

For this follow the steps given below.

1.Cretae a new table with two fields.
say table name - tblFileNames
Fields
FileName - Text
JobNum - Number
2. Create a list box in your main form
Name : lstFileName
Multi Select : Simple
RowsourceType:Table/Query
Rowsource:"SELECT tblFileNames.JobNum, tblFileNames.FileName
FROM tblFileNames;"
Bound Column : 2
Column Widths: 0";1"

3.Go to VBA editor. Set reference to "Microsoft Scripting Runtime"
library under Tools/References

4.In the Main form module enter the following procedure.
In the code replace the file path with actual file path.

Private Sub GetFileList()
Dim fso As New FileSystemObject

Dim f As File
Dim folFilesPath As Folder

Dim strFilesPath As String

'Replace the directory path as appropriate
strFilesPath = "C:\FilesForAttachment"

Set folFilesPath = fso.GetFolder(strFilesPath)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

rst.Open "tblFileNames", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

Dim strSQL as String
strSQL = "DELETE tblFileName.* FROM tblFileName;"

'Before getting files, delete old entries

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Get all files from the file path folder
For Each f In folFilesPath.Files
rst.AddNew
rst!filename = f.Name
'Try to extract JobNum in the File name
'If possible assign it.Comments given below.
'rst!JobNum = JobNum
rst.Update

Next

Set rst = Nothing

End Sub

5. In the open event of main form enter the one line code given below
i.e. you are calling the above procedure

getFileList

Save and close the form.

'Now open your form

If you have some files in the File Path given, then you
should be able to see the file names in the list box after
the form is open.

Now you may think why "JobNum" field is included in the table ?

You have mentioned that the file name of Schedule files
to be attached is in someway mapped to JobNum.

If you can extract the JobNum part in the file name using
some String handling functions, then while adding file names
in the table,you can also add related JobNum along with file name.

By this it is possible to show only related files for a Jobnum in
the list box when you move from one JobNum record to another.
For this you can write code to filter the list
in the Current Event of form.

Otherwise the list box will show all files for all Job Numbers.
========================================================
Now you can select files in the list box for attachment.

Now the issue is you want to select file names from the list box
which is in Main Form but the ItemID is in the sub form.

So before selecting files from the list box, you should make
the ItemId in the sub form for which files to be selected
as the current record.

Now assume for current ItemId you have selected some files.
Then Where and how to store the file names selected ?

You have suggested an ItemAttachment table which is correct.
Only a small alteration is required.

This table is to be used for storing file names for attachment
only tempororily till the mail is sent.This is my suggestion.

So create table with only two fields.
tblItemAttachment
--------------------------------------------------------
ItemID Integer
FileName Text

To add the file list in the above table, create a button
in the main form say cmdItemFileList(button name) and
caption "Add Item files".

In the On Click event of the button enter the following code.

Private Sub cmdItemFileList_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim intItemID as Integer

Dim varNumber as Variant


'Replace with actual table name in your code
rst.Open "tblItemAttachment", cnn, adOpenKeyset, adLockOptimistic,
adCmdTable

intItemId = Me.frmSub.Form!ItemId
'Delete old entries if any for the ItemID
Dim strSQL as String
strSQL = "DELETE tblItemAttachment.* FROM tblItemAttachment " & _
"WHERE (((tblItemAttachment.ItemID)=" & intItemID & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'Now add file names for attachment

For Each varNumber in lstfileName.ItemsSelected
rst.addNew
rst!ItemId = intItemId
rst!FileName = lstFileName.Itemdata(varNumber)
rst.update
Next

set rst = Nothing

End Sub

For each ItemId select files and click "Add item Files" button.

Before selecting files in the list for new ItemId you can deselect
the old selections by providing a button near list box and add code
like this.
-------------------------------------------------------------------------------------
'For next ItemID, refresh the list box for fresh selection.
Private Sub cmdDeselectAll_Click()
Me.lstFileName.RowSourceType = "table/query"
Me.lstFileName.RowSource = "SELECT tblFileName.JobNum,
tblFileName.FileName " _
& "FROM tblFileName ORDER BY [FileName];"

Me.lstFileName.Requery

End Sub
-------------------------------------------------------------------------------------
Having entered file names for all the ItemId in the sub form
now you can send mail for current JobNum.

- Creating the email
- Pausing the application to allow the user to confirm each email

For creating the mail the following data is required.

Recipient Mail Address
Message subject
Message Body
Message Attachments

If you call the procedure given below in the On Click
Event property of Send mail button in your form,
Outlook will open for each ItemId with all the particulars
supplied

Private Sub SendMessage()
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strFilePath As String
Dim strFilePathAndName As String
Dim strSubject As String
Dim strBodyMessage As String

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

'Replace with actual path.
strFilePath = "C:\FilesForAttachment\"

'Create the Outlook session
Set objOutlook = CreateObject("Outlook.Application")

'Create the message
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


Dim rstFile As ADODB.Recordset
Set rstFile = New ADODB.Recordset

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

'Replace with actual table name in your code
rstFile.Open "tblItemAttachment", cnn, adOpenKeyset, adLockOptimistic,
adCmdTable

'For looping all itemId's in the sub form create a recordset
Dim rst As DAO.Recordset
Set rst = Me.frmSub.Form.RecordsetClone

'For each itemId, open outlook with Mail,subject
'Body and Attachments and display to send
Do Until rst.EOF
rst.MoveFirst

With objOutlookMsg
'Add the To recipient(s) to the message

strTo = DLookup("EMail", "tblCompany", "CompanyID = " &
Me.frmSub.Form!MfrID)

Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = olTo

'Add the CC reipient(s) to the message
'Set objOutlookRecip = .Recipients.Add(strCc)
'objOutlookRecip.Type = olCC

'Add the BCC recipient(s) to the message
'Set objOutlookRecip = .Recipients.Add(strBcc)
'objOutlookRecip.Type = olBCC

'Set the Subject, Body and Importance of the message
'For strBody assign with string data if any from the form
'or other means
.Subject = strSubject
.Body = strBodyMessage
.Importance = olImportanceNormal

'Add attachments to the message.
'Find the attachment files using rstFile
'Then attach with the message
'If recordset is already filtered remove it.
rstFile.Filter = adFilterNone
'Filter for current ItemID
rstFile.Filter = "ItemId = " & rst!ItemId
Do Until rstFile.EOF
strFilePathAndName = strFilePath & rst!FileName
Set objOutlookAttach = .Attachments.Add(strFilePathAndName)
Loop
'This code will open outlook and pause
objOutlookMsg.Display

'To send msg directly use the code as shown.
'.Send
End With
rst.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing