VB for Excel, anyone?

jmc · 30th March 2008, 02:13 AM

I need a bit of help, if anyone can!
I'm working on setting up an Excel workbook (for work), which has an automated mailing function built in to run as a macro. Running the macro will create and send 4 separate emails, each of which will contain a specific worksheet as an attachment. I Googled a VB script which sent 1 mail (containing the first sheet of a workbook), and hacked it (through trial and error

) into the following:

Code:

Sub Send4Sheets_ActiveWorkbook()
'Creates 4 new Workbooks Containing 1 Sheet in each (i.e. Goods, Leaflets, Goods Pending and Leaflets Pending) _
 and sends as mail attachments.
 
    ThisWorkbook.Sheets(1).Copy
        
    With ActiveWorkbook
         .SendMail Recipients:="me@whatever.com", _
          Subject:="Request for Goods " & Format(Date, "dd/mmm/yy")
         .Close SaveChanges:=False
    End With

    ThisWorkbook.Sheets(2).Copy
    
    With ActiveWorkbook
         .SendMail Recipients:="me@whatever.com", _
          Subject:="Request for Leaflets " & Format(Date, "dd/mmm/yy")
         .Close SaveChanges:=False
    End With

    ThisWorkbook.Sheets(3).Copy
    
    With ActiveWorkbook
         .SendMail Recipients:="me@somewhere.com", _
          Subject:="Uncleared Previous Order Items - Goods " & Format(Date, "dd/mmm/yy")
         .Close SaveChanges:=False
    End With

    ThisWorkbook.Sheets(4).Copy
    
    With ActiveWorkbook
         .SendMail Recipients:="me@somewhere.com", _
          Subject:="Uncleared Previous Order Items - Leaflets " & Format(Date, "dd/mmm/yy")
         .Close SaveChanges:=False
    End With
End Sub

And, wonder of wonders, it works! I'm quite chuffed with myself!

So, I'm a complete stranger to VB and I was wondering if any gurus could help me with a couple of things...
The script, as you can see, obviously contains 4 separate instructions for processing Sheets (1), (2),(3) and (4). 1 & 2 go to one address, 3 & 4 to another. Can these 4 instructions be combined into 2? I guess it's gotta be something in the ".Sheets(1)." brackets, but I can't figure out the syntax!
Finally - the ".Close SaveChanges:=False" bit seems to mean that the workbooks created and attached to the mails are titled only with the default "Book1", "2", "3" etc etc name. Is it possible to specify a name for these? Obviously, I can edit the "Subject:=..." to reflect the title of the workbook, but my Boss is a bit antsy that the Recipients will be too nit-picky, and may throw them back at us if they are not properly named!

Thanks for any help/suggestions.
Cheers (and thanks for listening! Heheheh).
John Mc

jmc · 30th March 2008, 10:06 PM

Ten minutes after posting, I finally hit on the correct Google search string to find just what I was looking for

In case anyone else has a need, Ron's Excel page has LOTS of tips and example scripts for VB-illiterates like me! Now, I just need to figger out how to get it to work in our system at work.

John Mc