emailing files to specific accounts automatically

[phooey66]

I have a set of approx 150 files in Excel where each one needs to be sent to a specific account.

for example;
P1234.xls needs to be sent to 1234acc@mydomain.com
M9876.xls needs to be sent to 9876acc@mydomain.com
etc.

Is there an easy way to do this?

 

[CautionMP]

Here is a starting point with the assumption that the Excel workbooks are in the same directory.

Copy the following code and paste it into a new module in a new workbook:

Public Function MailFromDir()
Dim wkbToMail As Workbook
Dim strPath As String, strFile As String, strEmail As String

'This is the path where all the files are located
strPath = "Drive:\Folder\"
'Now add the .xls extension to the path
strPath = strPath & "*.xls"

'The first Dir statment needs the full path and extension
strFile = Dir(strPath)

'Keep calling the dir statement until it returns nothing
Do
  'Open the workbook to mail
  Set wkbToMail = Workbooks.Open(strFile)
  'Strip the .xls off the filename
  strEmail = Left(strFile, (InStr(strFile, ".") - 1))
  'Build the full email address
  strEmail = strEmail & "acc@mydomain.com"
  'Mail the newly opened workbook
  wkbToMail.SendMail strEmail, "Put Subject Here"
  'Close the workbook
  wkbToMail.Close
  'Check for the next workbook to process
  strFile = Dir
Loop Until strFile = ""

'Clean up
Set wkbToMail = Nothing
End Function

A couple of notes:[ol][li]This was done in O2K[/li][li]Don't save the workbook that holds this code in the same directory as the data.[/li][li]I made this a Function so you could create a Menu Bar Button that could point to it[/li][li]You will probably need to add a little bit of error handling if you choose to go this route.[/li][/ol]
Hope this helps,
CMP

Instant programmer, just add coffee.