Email output variable

**jlang11** · 11-27-2013, 01:03 PM

ok, Im stuck again. You guys were awesome last time and the help is much appreciated.

I have a macro that creates an email that advises members when they have contributed funds or received a refund to/from the club. My spreadsheet headings are Email address, Name, 1st contribution, 1st date, 2nd cont, 2nd date, 3rd cont, 3rd date, 4th cont, 4th date. Refunds show as a negative number, despite it saying "contribution".

What I dont want to happen is if someone contributed only 2 times, for the 3rd cont and date and 4th, etc, to print in the email... They have all made at least 1 contribution (Original Contribution Received).

Here is the message part of the macro. I think it would just be a series of IF statements after the initial contribution, but not sure how to format it.

	Msg = "Dear club member, " & vbNewLine & vbNewLine & _
" Please see below the dates and amounts you have contributed to date  " & vbNewLine & vbNewLine & _
" Transaction  " & Format(Sheets("Sheet1").Range("D" & i).Value, "MM/DD/YYYY") & " -- " & _
Format(Sheets("Sheet1").Range("C" & i).Value, "$#,##0.00; ($#,##0.00)") & " -- Original Contribution Received" & vbNewLine & _
" Transaction  " & Format(Sheets("Sheet1").Range("F" & i).Value, "MM/DD/YYYY") & " -- " & _
Format(Sheets("Sheet1").Range("E" & i).Value, "$#,##0.00; ($#,##0.00)") & vbNewLine & _
" Transaction  " & Format(Sheets("Sheet1").Range("H" & i).Value, "MM/DD/YYYY") & " -- " & _
Format(Sheets("Sheet1").Range("G" & i).Value, "$#,##0.00; ($#,##0.00)") & vbNewLine & _
" Transaction  " & Format(Sheets("Sheet1").Range("J" & i).Value, "MM/DD/YYYY") & " -- " & _
Format(Sheets("Sheet1").Range("I" & i).Value, "$#,##0.00; ($#,##0.00)") & vbNewLine & _
'the line below is just to make the sum line under the dollar amounts line up
"                                                     _________" & vbNewLine & _
"                                                      " & Format(Sheets("Sheet1").Range("K" & i).Value, "$#,##0.00; (#,##0.00)") & " -- Total as of this date " & vbNewLine & vbNewLine & _

Is something like this what I should be doing??

IF
   Format(Sheets("Sheet1").Range("F" & i).Value = "", (goto next)

else if 
   Format(Sheets("Sheet1").Range("H" & i).Value = "", (goto next)

else if
Format(Sheets("Sheet1").Range("J" & i).Value = "", (goto next)

End IF

"                             _________" & vbNewLine & _
        "                                                      " & Format(Sheets("Sheet1").Range("K" & i).Value, "$#,##0.00; (#,##0.00)") & " -- Total as of this date " & vbNewLine & vbNewLine & _

Here is the current output from one:

 Transaction  02/03/2011 -- $115.60 -- Original Contribution Received
 Transaction  02/03/2012 -- $83.45
 Transaction  02/15/2013 -- (67.70)
 Transaction   -- 
                                      _________
			 $131.35 -- Total as of this date

Id rather not have the additional "Transaction --" and/or the space between the dollar amounts and the total if possible. Some people will only have 1 "transaction" and dollar amount.

**TMS** · 11-27-2013, 01:41 PM

I think it's a case of seeing the wood for the trees. I would suggest you build each of the transaction parts and then it's easier to see if you need the part and add it.

Regards, TMS

**jlang11** · 11-27-2013, 02:43 PM

Not sure what you mean. I will need it for some, but not others. Some people have 1 transaction, and some have 4. Some 2 and some 3.

I guess I could break them into different sheets, Sheet1 with 1 transaction, Sheet2 with 2 transactions, etc, and copy the macro 4 times and delete the extra lines for each page...

**TMS** · 11-27-2013, 04:43 PM

It would look something like this:

Option Explicit

Sub Test()

'
' Transaction  02/03/2011 -- $115.60 -- Original Contribution Received
' Transaction  02/03/2012 -- $83.45
' Transaction  02/15/2013 -- (67.7)
' Transaction   --
'                           _________
'                            $131.35 -- Total as of this date

Dim Msg As String
Dim sDear As String
Dim sTran1 As String
Dim sTran2 As String
Dim sTran3 As String
Dim sTran4 As String
Dim sLine As String
Dim sTotal As String
Dim i As Long: i = 2


With Sheets("Sheet1")

    ' build the greeting
    sDear = "Dear club member, " & vbNewLine & vbNewLine & _
    " Please see below the dates and amounts you have contributed to date" & vbNewLine & vbNewLine
    
    ' build payment 1
    sTran1 = " Transaction  " & Format(.Range("D" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("C" & i).Value, "$#,##0.00; ($#,##0.00)") & " -- Original Contribution Received" & vbNewLine
    
    ' build payment 2
    sTran2 = " Transaction  " & Format(.Range("F" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("E" & i).Value, "$#,##0.00; ($#,##0.00)") & vbNewLine
    
    ' build payment 3
    sTran3 = " Transaction  " & Format(.Range("H" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("G" & i).Value, "$#,##0.00; ($#,##0.00)") & vbNewLine
    
    ' build payment 4
    sTran4 = " Transaction  " & Format(.Range("J" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("I" & i).Value, "$#,##0.00; ($#,##0.00)") & vbNewLine
    
    ' build underline
    sLine = Space(50) & "_________" & vbNewLine
    
    ' build total
    sTotal = Space(50) & Format(.Range("K" & i).Value, "$#,##0.00; (#,##0.00)") & " -- Total as of this date " & vbNewLine & vbNewLine

    ' build message
    Msg = sDear & sTran1
    If .Range("F" & i).Value <> "" Then Msg = Msg & sTran2
    If .Range("H" & i).Value <> "" Then Msg = Msg & sTran3
    If .Range("J" & i).Value <> "" Then Msg = Msg & sTran4
    Msg = Msg & sLine
    Msg = Msg & sTotal

End With

MsgBox Msg

End Sub

Regards, TMS

**TMS** · 11-28-2013, 06:28 AM

Actually, I think this version aligns the positive and negative values a little better:

Option Explicit

Sub Test()

'
' Transaction  02/03/2011 -- $115.60 -- Original Contribution Received
' Transaction  02/03/2012 -- $83.45
' Transaction  02/15/2013 -- (67.7)
' Transaction   --
'                           _________
'                            $131.35 -- Total as of this date

Dim Msg As String
Dim sDear As String
Dim sTran1 As String
Dim sTran2 As String
Dim sTran3 As String
Dim sTran4 As String
Dim sLine As String
Dim sTotal As String
Dim i As Long: i = 2


With Sheets("Sheet1")
    sDear = "Dear club member, " & vbNewLine & vbNewLine & _
    " Please see below the dates and amounts you have contributed to date" & vbNewLine & vbNewLine
    
    ' build payment 1
    sTran1 = " Transaction  " & Format(.Range("D" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("C" & i).Value, " $#,##0.00;($#,##0.00)") & " -- Original Contribution Received" & vbNewLine
    
    ' build payment 2
    sTran2 = " Transaction  " & Format(.Range("F" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("E" & i).Value, " $#,##0.00;($#,##0.00)") & vbNewLine
    
    ' build payment 3
    sTran3 = " Transaction  " & Format(.Range("H" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("G" & i).Value, " $#,##0.00;($#,##0.00)") & vbNewLine
    
    ' build payment 4
    sTran4 = " Transaction  " & Format(.Range("J" & i).Value, "MM/DD/YYYY") & " -- " & _
    Format(.Range("I" & i).Value, " $#,##0.00;($#,##0.00)") & vbNewLine
    
    ' build underline
    sLine = Space(48) & "_________" & vbNewLine
    
    ' build total
    sTotal = Space(48) & Format(.Range("K" & i).Value, "$#,##0.00; (#,##0.00)") & " -- Total as of this date " & vbNewLine & vbNewLine

    ' build message
    Msg = sDear & sTran1
    If .Range("F" & i).Value <> "" Then Msg = Msg & sTran2
    If .Range("H" & i).Value <> "" Then Msg = Msg & sTran3
    If .Range("J" & i).Value <> "" Then Msg = Msg & sTran4
    Msg = Msg & sLine
    Msg = Msg & sTotal

End With

MsgBox Msg

End Sub

Regards, TMS

**TMS** · 11-28-2013, 05:31 PM

@jlang11:

I notice you have been on the board since I offered this solution. As I spent some time putting it together for you, I would be very interested to know if it worked and did what you hoped for.

Regards, TMS

**jlang11** · 11-29-2013, 09:13 AM

My apologies. I received an email alerting me there was a post and was anxious to see what it was, but due to the holiday I was not at my computer to test it. I should have some time very shortly to test it and I appreciate your effort. The code looks very intereting and I am anxious to see what it does. Ill post back in a few and let you know.

Thanks again!

**jlang11** · 11-29-2013, 09:45 AM

That works perfectly!!!! Thank you so much!!!!

**TMS** · 11-29-2013, 09:50 AM

You're welcome. Thanks for the rep.

Please note the "i = 2" which is just in there for testing

**jlang11** · 11-29-2013, 10:09 AM

Which worked out perfectly because I also had i = long (For i = 2 To LastRow) when it pulls the email address from column a.

Thanks again!!

Email output variable

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Re: Email output variable

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Paste variable output (SQL Output) into a single cell.

Variable Print Output (display as well if possible)

Using Match function output as a variable

Compare two email lists and output non-matching

Do Loop with variable output

Bookmarks

Bookmarks

Posting Permissions