Macro Help to Name Email Spreadsheet

[Emb21]

Hello all,

Using Excel 2003. This macro emails an excel spreadsheet as an attachment but doesn't give the attachment a specific name (calls it book4.xls) so I am trying to accomplish the following:

1. Name the file I am sending to what cell B4 text has on the same sheet (this macro calls it a generic name, i.e. Book4.xls)

2. Add the text from B4 name to the line in subject along with the format date (see line "arg2:")

For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Dining Summary Transfer" Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End If
Next sh

With ActiveWorkbook

Application.Dialogs(xlDialogSendMail).Show arg1:="", _
arg2:="Dining Summary Transfer Sheet" & Format(Date, " dd/mmm/yyyy")
End With

Any help would be appreciated.

Thanks,
Eric from PA

 

[Emb21]

Bump for help...any suggestions?