Recover Lost Data with MicroSD Card Recovery : Troubleshoot:Adding Cell Values to a Macro when sending an email

Adding Cell Values to a Macro when sending an email

Hello All,
I have a macro where I need to add 2 cell values when the email is triggered.
I can't get my code to pick up the needed cells and not sure where I'm going wrong.
Here's the code I have written and the worksheet I'm trying to pull the data from:

Sub Test5()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Columns("j").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "f").Value) = "yes" _
           And LCase(Cells(cell.Row, "g").Value) <> "sent" Then
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                        "Please note the Named Associate is on an Intermittent FMLA " & _
                        "with effective date("d") and reason('h")."


                .Send
            End With
            On Error GoTo 0
            Cells(cell.Row, "g").Value = "sent"
            Set OutMail = Nothing
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

J represents the email address I'm sending to.
F represents the trigger, when "yes" send the email
G represents "sent" so the email is only sent once
A represents the employees name

I need to insert value from cell D - the effective date and cell H - the reason.
I need the body of the email to say:

Please note the Named Associate is on an Intermittent FMLA the with effective date(cell d) and reason( cell h).

So in the case of John Doe it would say: Please note the Named Associate is on an Intermittent FMLA the with effective date 8/15/2013 and reason Care of Mother.

So in the case of Mary Jane it would say: Please note the Named Associate is on an Intermittent FMLA the with effective date 8/16/2013 and reason Personal Care.

Here is my worksheet if needed: Hope I was clear enough but I'm at my wits end trying to get this to work.

Associate
Manager
FMLA Type:   I=Intermittent
R=Reduced
FMLA Effective Date
Date Paperwork Requested from Bethpage
FMLA Type Trigger
FMLA Type Trigger - Sent
Reason

John Doe

I
8/15/2013
9/1/13
YES
sent
Care for Mother

Mary Jane

I
8/16/2013
9/2/13
YES
sent
Personal Care

Anwsers to the Problem Adding Cell Values to a Macro when sending an email

Download Error Fixer for Free Now

Hi Tigershoes,

Try this, I am not saying it is already what you need but at least I had some return of it.

HTH

Wkr,

JP Ronse

Sub Test5()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("j").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "f").Value) = "yes" _
           And LCase(Cells(cell.Row, "g").Value) <> "sent" Then

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                        "Please note the Named Associate is on an Intermittent FMLA " & _
                        "with effective date: " & Cells(cell.Row, "C") & " and reason: " & Cells(cell.Row, "H") & "."


                .display
            End With
            OutMail.display
            On Error GoTo 0
            Cells(cell.Row, "g").Value = "sent"
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Using Windows troubleshooter:

Click on Search on Charm bar and type Troubleshooting. Click on the very first option.
A new window will open. Click View all from the left pane.
Scroll down to the bottom and click on Windows update.
Troubleshooter window for Windows update will pop up. Click on NEXT. It may ask you for administrative permission.
The troubleshooter will automatically detect problems and fix it.
Now, close this window and try download/install the app which was previously showing error.

Another Safe way to Fix the Problem: Adding Cell Values to a Macro when sending an email:

How to Fix Adding Cell Values to a Macro when sending an email with SmartPCFixer?

1. Download Error Fixer. Install it on your system.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.

Recover Lost Data with MicroSD Card Recovery

Thursday, March 31, 2016

Troubleshoot:Adding Cell Values to a Macro when sending an email

Adding Cell Values to a Macro when sending an email

Anwsers to the Problem Adding Cell Values to a Macro when sending an email

Using Windows troubleshooter:

Another Safe way to Fix the Problem: Adding Cell Values to a Macro when sending an email:

How to Fix Adding Cell Values to a Macro when sending an email with SmartPCFixer?

No comments:

Post a Comment