Email Excel spreadsheet to people defined by user input

agb136

New Member
Joined
Jan 26, 2006
Messages
34
I have a simple problem, I think. I want to save a file and name it based on two cells and a timestamp. I got that working. Now I want the macro to email to myself (always) and the email addresses the user defines in the input box. I can get it to email me but I can not get the second part.

Here is my code:

Rich (BB code):
Sub Email()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wbname As String
    Dim a As String
    Dim b As String
    Dim EmailAddress As String
      
    Application.ScreenUpdating = False
    Set wb1 = ActiveWorkbook
    a = Range("B2")
    b = Range("F2")
    wbname = "C:/" & a & "-" & b & " " & _
    Format(Now, "mm-dd-yy h'mm") & ".xls"
    wb1.SaveCopyAs wbname
    Set wb2 = Workbooks.Open(wbname)
    
    With wb2
        EmailAddress = InputBox("Please enter the email addresses of the people you would like to send this file to: (Example: acb123@aol.com; acb132@yahoo.com; avb@gmail.com)")
        .SendMail "aaron918@aol.com" + "; " + EmailAddress, _
                  "Action Required: What If"
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With
    Application.ScreenUpdating = True

End Sub

Thank you in advance for your help.

agb
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,114,671
Messages
5,549,344
Members
410,910
Latest member
DessertDiva
Top