Emailing with multiple attachments.

[babyatx13]

Currently I am writing data to a table based on an Excel file (name could be
anything), then changing the name of the file to show the date, based on the
date given in the file, and then moving it to a designated project folder,
based on the project number listed in the file, and emailing the file with
the new file name as an attachment.
Working code:
============================================
Function ProcDaily1()
Dim strFile As String
Dim strNewFile As String
Dim strFolder As String
Dim file As String, sfol As String, dfol As String
Dim fso As Object
Dim fol As String
Dim xProNo
‘xProNo is the project number I get from the file.
Dim xPath
Dim xNewPath
Dim xDrive
Dim Answer
Dim Counter
Dim strNewFN As String

xPath = "P:\Daily Temp\"
strFile = Dir(xPath & "*.xls", vbNormal)

strNewFN = strNewFile
xDrive = "P:\"
xNewPath = xDrive & xProNo & "\Daily\"
file = strFile
sfol = xPath
dfol = xNewPath
fol = dfol
Set fso = CreateObject("Scripting.FileSystemObject")
Answer = 0
Counter = Chr(65)
Do While Answer = 0
If Not fso.FileExists(dfol & strNewFN & ".xls") Then
'if no file exists in destination folder then
Name xPath & strFile As xNewPath & strNewFN & ".
xls"
Answer = 1
Email ‘calls email function below
Else 'if file does exsist in destination folder then
strNewFN = strNewFile & " " & Counter 'change the
name of the file
Counter = Chr(Asc(Counter) + (1)) 'add one to the
counter
Answer = 0 'then ask the question again
End If
Loop
End Function

********************************************

Function Email()
Dim blnSuccessful As Boolean
Dim strHTML As String
Dim strSubject As String
Dim strTo As String

Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object

Set oLook = CreateObject("outlook.Application")
Set oMail = oLook.CreateItem(0)

With oMail
.To = "<[email protected]>"
.Subject = "Daily Report for Project number " & xProNo
.Attachments.Add (xNewPath & strNewFN & ".xls")’ these are Dimed
publicly
.Body = "Attatched is the Daily Report that was posted for project
number " & xProNo
.Send
End With

Set oMail = Nothing
Set oLook = Nothing

Exit Function
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message
is: " & Err.Description
Resume Error_out
Error_out:

End Function
=====================================================
This works great, but for each file it sends one email, I want to be able to
send multiple attachments in one email. I know I will have to move the
email function out of my loop to achieve multiple attachments I’m just not
sure how to get the files attached when the file name keeps changing. I
also moved a copy of all files to a temporary email folder with the project
number and the date as the new file name, and I want to send the contents of
the folder in a single email message.

Can anyone direct me in the right direction?

Thanks
K Board

 

[Guest]

Instead of calling Email in your loop, just build a String or populate an Array
with all of the file paths. Then call Email at the end of your ProcDaily
procedure passing the string or Array.

Then, in your Email procedure, examine the String or Array in a loop and add
an attachment for each filepath specified.

Steve

 

[babyatx13 via AccessMonster.com]

Thanks Steve, as I stated before I knew I had to move my email procedure out
of my loop, I was having a hard time capturing the file name in the temp
Email folder but I got it figured out yesterday. After I capture the file
name in the temp folder I attach it and then delete it so it will capture the
next file name. I loop it until the folder is empty.

Thanks again for posting a reply.
K Board

Instead of calling Email in your loop, just build a String or populate an Array
with all of the file paths. Then call Email at the end of your ProcDaily
procedure passing the string or Array.

Then, in your Email procedure, examine the String or Array in a loop and add
an attachment for each filepath specified.

Steve

Currently I am writing data to a table based on an Excel file (name could be
anything), then changing the name of the file to show the date, based on the

[quoted text clipped - 99 lines]

Thanks
K Board