Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011

    Unanswered: E-mail Addresses using VBA


    I am trying to concatenate e-mail addresses in a spreadsheet into 1 cell and then through using vba export them to the 'To' line of an email.

    I am resisting listing the e-mail address in the spreadsheet as there are over 80 tabs and numerous e-mail addresses

    Acompany@mail,com; Bcompany@mail.,com;

    However my code just puts quotation marks around the 3 e-mail addresses once in the 'To' line of the e-mail

    The red part of the VBA is where my code errors

    Anay davice would be greatly appreciated


    Sub Mail_Every_Worksheet()
    'Working in 97-2010
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim I As Long

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

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

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

    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("Af3").Value Like "?*@?*.?*" Then

    Set wb = ActiveWorkbook

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

    With wb
    .SaveAs TempFilePath & TempFileName & FileExtStr, _
    On Error Resume Next
    For I = 1 To 3
    .SendMail Email = "AF3", Subject:=("Monthly Overtime Report")
    If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
    .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    End If
    Next sh

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

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if its VBA in Excel then usually its best to post this in t he excel forum
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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