Cell Value in Email Body?

Willie03

Board Regular
Joined
Jun 21, 2013
Messages
50
Hello,

I have this code but and I can't this out: it just keeps looping and doesn't stop... Its also not inserting the signature in each email...

Can someone let me know what I am doing wrong please?



Code:
Sub Notify()    Dim name, email, evnt, status As Range
    Dim SigString As String
    Dim Signature As String
    Dim last_row As Integer
    Dim LastRow As Long
    
    LastRow = Range("C2:C200").End(xlDown).Row


        SigString = Environ("appdata") & _
                "\Microsoft\Signatures\Will.txt"


    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If


    For Each name In Range(("B2"), Range("B2").End(xlDown))
        For Each email In Range(("C2"), Range("C2").End(xlDown))
            For Each evnt In Range(("E2"), Range("E2").End(xlDown))
                For Each status In Range(("F2"), Range("F2").End(xlDown))


    On Error Resume Next


        With CreateObject("Outlook.Application").CreateItem(0)
            .To = email.Value
            .CC = email.Offset(0, 1).Value
            .Subject = "Information You Requested"
            .Body = "Dear " & name.Value & "," & vbNewLine & vbNewLine & _
                "As you may know the 2014 race started on November 4th and will be open until November 15th in order for you to select your candidate." & vbNewLine & vbNewLine & _
                "Your 2014 election is now On Hold since you have a previous " & evnt.Value & " event with a " & status.Value & " status in Workday. " & vbNewLine & vbNewLine & _
                "In order for you to be able to finalize your Elections you need to finalize the event above as soon as possible." & vbNewLine & vbNewLine & _
                "If you have any questions about this task please call us at 1-877-777-7777 option 8" & vbNewLine & vbNewLine & _
                "Regards" & vbNewLine & vbNewLine & _
                "Department"
            .Display
            '.Attachments.Add "C:\test.txt"
            '.Send
            End With
            
                Next
            Next
        Next
    Next
    
End Sub


Function GetBoiler(ByVal sFile As String) As String
    '**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function
 

DeBeuz

Active Member
Joined
Jun 6, 2012
Messages
448
Hi,

It seems to me that looping is caused by your 4 nested For loops
Code:
    For Each Name In Range(("B2"), Range("B2").End(xlDown))
        For Each Email In Range(("C2"), Range("C2").End(xlDown))
            For Each evnt In Range(("E2"), Range("E2").End(xlDown))
                For Each Status In Range(("F2"), Range("F2").End(xlDown))
If there are 5 values in B, 6 in C, 4 in E and 8 in F, this would create 5*6*4*8 = 960 emails; is that what you wanted?
Also it seems weird to me that you are addressing the same name with all email addresses found, usually one name is connected to one email.
Please explain what it is you want to have done.

I do not know how to have the signature added using vba, except from putting it into the code.
I think Outlook is normally entering the signature in the body before you get a change to fill in your text; It does not add it after pressing Send.
If I'm correct, you should hard-code your signature in your vba.

Succes,

Paul
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top