Results 1 to 3 of 3

Thread: Excel Maccro

  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Maccro

    Hi all,

    One of my colleagues has the macro below, she wants to modify it and enter another email address on cell L1 so the sheet get emailed to two people, what do I need to do to make it work?

    Many thanks,


    Sub Mail_Every_Worksheet()
    'Working in 97-2007
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    ' New variables and constant
    Dim wbList As Workbook
    Dim shList As Worksheet
    Dim r As Long
    Dim strPassword As String
    Dim i As Integer
    Dim j As Integer
    Const strChars = "ABCDEFGHIJKLMNOPQSTUVWXYZabcdefghijklmnopqrstuvwx yz0123456789"





    ' Initialize random generator
    Randomize


    TempFilePath = Environ$("temp") & "\"


    If Val(Application.Version) < 12 Then
    'You use Excel 97-2003
    FileExtStr = ".xls": FileFormatNum = -4143
    Else
    'You use Excel 2007
    FileExtStr = ".xlsm": FileFormatNum = 52
    End If


    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With


    ' Workbook for passwords
    Set wbList = Workbooks.Add(Template:=xlWBATWorksheet)
    Set shList = wbList.Worksheets(1)


    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("K1").Value Like "?*@?*.?*" Then
    sh.Copy
    Set wb = ActiveWorkbook
    ' Make password
    strPassword = sh.Range("K2")


    TempFileName = "Sheet " & sh.Name & " of " & _
    ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")


    With wb
    .SaveAs TempFilePath & TempFileName & FileExtStr, _
    FileFormat:=FileFormatNum, Password:=strPassword
    On Error Resume Next
    .SendMail sh.Range("K1").Value, "Staff Reallocation Detail PLEASE EMAIL QUERIES - PLEASE CONTACT REGIONAL COORDINATOR TO VERBALLY RECEIVE YOUR PASSWORD"
    On Error GoTo 0
    .Close SaveChanges:=False
    End With

    Kill TempFilePath & TempFileName & FileExtStr


    ' Next row
    r = r + 1
    ' E-mail address in column K cell K1
    shList.Range("A" & r) = sh.Range("K1")
    ' Filename in column A cell A3
    shList.Range("B" & r) = TempFileName & FileExtStr
    ' Password in column K cell K2
    shList.Range("C" & r) = strPassword
    End If
    Next sh




    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could it be as simple as adding the line:

    .SendMail sh.Range("L1").Value, "Staff Reallocation Detail PLEASE EMAIL QUERIES - PLEASE CONTACT REGIONAL COORDINATOR TO VERBALLY RECEIVE YOUR PASSWORD"

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Or set a variable to the email address, then test L1 for an email address and add it with a semicolon separator between addresses (me@mymail.com;you@yourmail.com). Now use the variable in the SendMail.

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •