Sleeper: Emailing Excel Tabs

[dave26ny]

Hello, I have a project due at work and have no idea where to start. Basically, I have an Excel workbook with about 50 tabs. Each tab has a different vendor number for it's tab name. Each tab needs to be made into its own workbook (which i can do through vba code) however it then needs to be emailed to that specific vendor contact. I dont want to hardcode the vendor email address into the vba code, since the email addresses change frequently. Rather, we maintain another Excel file that has two columns, one for the vendor number and one for the vendor email address. I was hoping that this file could somehow be used.
Right now I have code to open up the main spreadsheet, go through each of the 50 tabs and create 50 separate workbooks (one for each vendor) and auto-save each with the vendor number as the file number.
After that i am stuck as to how to use the email distribution list to send these files automatically. Any help anyone could give me is greatly appreciated. I have samples of the files if needed. Thanks so much, Dave.

[Killian]

Hi and welcome to VBAX

I would be inclined to put some code in the file that has the vendor number/mail addresses.
You could select the range and for each row, create a mail object, attach the file and send.
Using Outlook directly to send them it straight forward (Note: I haven't included any checks for the exisistence of the file, path etc)

Sub MailAllWorkbooks()
'If you integrate this with the filesave routine
'I guess you'll already have a filepath
Const strFILEPATH As String = _
    "C:\Documents and Settings\Killian\Desktop\FilesToMail\"
'add a reference (VBE>Tools>References) to the
'Microsoft Outlook Object Model
Dim objOLApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim rngRow As Range
Set objOLApp = New Outlook.Application
'select the Vendor IDs you want to send
    'the mail addresses should be in the next column
    For Each rngRow In Selection
        Set objMail = objOLApp.CreateItem(olMailItem)
        With objMail
            .Recipients.Add rngRow.Offset(0, 1).Text
            .Attachments.Add strFILEPATH & rngRow.Text & ".xls"
            .Body = "Some text for the mail body..."
            .Send
        End With
    Next
    objOLApp.Quit
End Sub

However, if you have a recent version/Service pack installed, the "Outlook Object Model Guard" will come into play, bringing up a warning message for code access to Outlook, then a timed message for each mail sent, that you'll have to click :-(
These are security features that are a good thing - unless you want to code for Outlook - Have a look in the Outlook forum for posts regarding this and the use of 3rd party addins, like Redemption