Automated email based on Cell value

[z168]

I need help to be able to email based on cell values of the column.



Based on Column M to P with a "Y" value, I need to send an automated email with the Column headers as the subject together with an patient account in the row where the Y was. And I can provide the email directly into the code or have it in a separate tab with the file

 

[z168]

anyone?

 

[apo]

Hi..

It is not 100% clear to me what you are trying to do.. but i have made some tested and working code that does the following:

(Assuming that columns M:P on sheet1 are as you showed above.. and also assuming that the email address for each patient is in Column A and there name is in Column B.)

Below code will Create an email if any of the columns (M:P) have a "Y" in them.
The Subject for each email will be the Column header.
The email address for each will be the value in Column A.
The Body will contain "Hi" and then the persons name which is the value in Column B.

You just need to add the rest of your body..

Private Sub CommandButton1_Click()
Dim temp As String
    Dim ws As Worksheet
    Dim i As Long
    Dim rowNr As Integer
    Dim strTo As String
    Dim Nme As String


    strTo = ""


Set ws = ActiveSheet
rowNr = 2
Do While rowNr <= Sheets("Sheet1").UsedRange.Rows.Count




    For i = 13 To 16 Step 1
        If ws.Cells(rowNr, i).Value = "Y" Then
        temp = ws.Cells(1, i).Value
        strTo = ws.Cells(rowNr, 1).Value
        Nme = ws.Cells(rowNr, 2).Value
 
'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
        


'Display the email message
With objMailItem
.To = strTo
.Subject = temp
.Body = "Hi " & Nme & "," & Chr(10) & Chr(10) & _
"Blah Blah (enter body text here)"
.Display 'Change to .Send if you want to just send it.
End With


'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing


   
    End If
    Next i
    rowNr = rowNr + 1
    Loop


End Sub

Note: For testing.. I ran this Sub from a Command Button on Sheet1 and had the Code in Sheet1

 

[z168]

Thank you Apo. Could you clarify the note for testing? I copied your code into the sheet1 and not the module but it doesnt work. I saved the file as .xlsm and as .xls and it doesnt work


This is how I need it to work


The email can be on the top of the column header. And column A would just be the account number as part of the email

Thanks again APO! I hope you can help me

 

[apo]

Hi..

Can you expand on 'doesn't work'.. what is it doing/not doing?

btw.. did you add a commandbutton (CommandButton1) which is used to run the Sub?

 

[z168]

Uh oh...thats my dilemma. I tried to understand how to add a commandbutton but I am sorely a novice in vba. So that explains why it doesnt work.

Can your code generate an email without any confirmation simply by typing a "Y" in the 3 columns?

 

[apo]

Hi..

Paste this code into Sheet1..

I have included the "Billed" column (Column P)..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tRow As Integer
Dim i As Integer
If Target.Column >= 17 Or Target.Column <= 12 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target = "Y" Or Target = "y" Then


tRow = Target.Row
i = Target.Column


Dim temp As String
Dim ws As Worksheet
Dim rowNr As Integer
Dim strTo As String
Dim Acc As String


strTo = ""


Set ws = ActiveSheet
rowNr = tRow


 If ws.Cells(rowNr, i).Value = "Y" Or ws.Cells(rowNr, i).Value = "y" Then
        temp = ws.Cells(2, i).Value
        strTo = ws.Cells(1, i).Value
        Acc = ws.Cells(rowNr, 1).Value
 
'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
        


'Display the email message
With objMailItem
.To = strTo
.Subject = temp
.Body = "Blah Blah (enter body text here)" & Chr(10) & _
"Account number is " & Acc


.Display 'Change to .Send if you want to just send it.
End With


'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing


    End If
 
End If
End Sub

The code will be run when a cell within the 4 columns (M:P) are changed.. meaning that if you type "Y" in one of those cells.. you will need to then focus on another cell for it to know there has been a change event... (press enter after typing "y" or whatever)..

If you get stuck.. let me know.. I can upload the working Excel file..

 

[z168]

Perfect! Thank you Apo!!!

Im curious - is it possible to be able to generate and send the email without user input of having to click SEND? Which means once the user keys-in a "Y" an automated email will be sent

 

[apo]

Change this line..

.Display

to

.Send

 

[z168]

Wow simply awesome! Thanks Apo!!!