none
Sending Mails using VBA RRS feed

  • Question

  • Hi

    I am looking to send a excel workbook to a specific person depending on a cell value.

    I have looked at various VBA codes but nothing really does the job.  Scenario:

    If cell e3 = 1012, then the workbook needs to mailed to aperson@samplemail.com, if cell e3 = 1008, then the workbook needs to mailed to bperson@samplemail.com, etc. If I can have some guidelines on how to this I would be grateful.

    I am currently have this code and it works great

    Sub Mail_Workbook()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
    If wb.FileFormat = 51 And wb.HasVBProject = True Then
    MsgBox "There is VBA code in this xlsx file that will be removed if you try to send this file." & vbNewLine & _
    "Save the file first as xlsm and then try the macro again.", vbInformation
    Exit Sub
    End If
    End If
    On Error Resume Next
    For I = 1 To 1
    wb.SendMail Array("aperson@samplemail.com", "bperson@samplemail.com", "cperson@samplemail.com"), Subject:=" Information " & wb.Sheets(1).Range("E3").Value

    If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
    End Sub

    But I would like it to work as above


    C Ward


    • Edited by Skybluekid Thursday, June 14, 2012 8:16 AM
    Thursday, June 14, 2012 8:10 AM

All replies

  • Your For loop is for ?- delete it

    paste this part:

    Dim Your_data: Date = wb.Sheets(1).Range("e3").Value
    Select Case Your_data
        Case 1012: wb.SendMail Array("aperson@samplemail.com"), Subject:=" Information " & Your_data
        Case 1008: wb.SendMail Array("bperson@samplemail.com"), Subject:=" Information " & Your_data
        Case 1013: wb.SendMail Array("aperson@samplemail.com", "cperson@samplemail.com"), Subject:=" Information " & Your_data
        Case Else: wb.SendMail Array("aperson@samplemail.com", "bperson@samplemail.com", "cperson@samplemail.com"), Subject:=" Information " & Your_data
    End Select


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved


    Thursday, June 14, 2012 9:21 AM
    Answerer