Solved Send an automatic email from within Excel if a formula value

October 15, 2017 at 02:56:25
Specs: Windows 7
0
down vote
favorite
Could you please help me to automatically send an email from Excel only when the formula value in column M (=IF(VAL.EMPTY(K15);"";MAX(K15-Today();0))>200. Unfortunately the Sheet1 code triggers the email code if the condition is met (>200) in formula value cell in column M if the date in column K is altered manually or by writing manually Not Sent in column N. Instead my goal would be: 1) to understand why this code in sheet1 doesn't send the email automatically as supposed to do (the only thing it does is to put Sent in column N without sending the email. This make me think that this code works) 2) to find the way to send the email automatically without changing anything manually in the cells in my sheet1.

H I J K L M N
Date Score Description Next Due Status Days till
expiration
15 28/09/2017 13 Medium Risk 25/07/2018 Valid 284 Sent
16 11/10/2017 13 Medium Risk 10/08/2018 Valid 300 Sent

'Sheet1 (FormulaValueChange)

Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = 200

'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("M15:M16")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook1(FormulaCell)
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell

ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description

End Sub

'Mail Code

Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook1(FormulaCell As Range)

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strto = "tom@something.abc"
strcc = ""
strbcc = ""
strsub = "Assessement reminders"
strbody = "Thanks a lot"
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
'You can add a file to the mail like this
'.Attachments.Add ("C:\test.txt")
.Display ' or use .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

message edited by TOMMASO


See More: Send an automatic email from within Excel if a formula value

Reply ↓  Report •

✔ Best Answer
October 19, 2017 at 16:27:48
Thanks for the instructions. Unfortunately, there is nothing for me to suggest because either I am not seeing any problem or perhaps I don't understand the problem. Here is what I did:

1 - Since one of the criteria required for the Worksheet_Calculate() event code to call the email macro is that the cells in Summary Column N contain Not Sent, I decided to populate Summary N16:N38 with Not Sent.

2 - Since there is a Worksheet_Change() and Worksheet_Calculate() macro associated with that sheet, I disabled them in order to manually change N16:N38 and populated Summary N16:N38 with Not Sent so that I had this:

             N
15      Not Sent
16      Not Sent
17      Not Sent
18      Not Sent
19      Not Sent
20      Not Sent
21      Not Sent
22      Not Sent
23      Not Sent
24      Not Sent
25      Not Sent
26      Not Sent
27      Not Sent
28      Not Sent
29      Not Sent
30      Not Sent
31      Not Sent
32      Not Sent
33      Not Sent
34      Not Sent
35      Not Sent
36      Not Sent
37      Not Sent
38      Not Sent

3 - I then re-enabled the Worksheet_Change() and Worksheet_Calculate() macros and clicked the Submit button on the Waterlow sheet. 8 emails were created. Back in the Summary sheet I saw the following.

          N
15      Sent
16      Sent
17      Sent
18      Sent
19      Not Sent
20      Sent
21      Not numeric
22      Not numeric
23      Not numeric
24      Not Sent
25      Sent
26      Sent
27      Not numeric
28      Not numeric
29      Not Sent
30      Sent
31      Not numeric
32      Not numeric
33      Not numeric
34      Not Sent
35      Not Sent
36      Not numeric
37      Not numeric
38      Not numeric

8 occurrences of Sent means that 8 emails should have be created, right? That's what I got.

Am I missing something?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
October 16, 2017 at 16:18:51
First, a posting tip:

Please click on the How-To Link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

As for your questions...

Before we look at the code too deeply, I'm confused by this requirement:

to find the way to send the email automatically without changing anything manually in the cells in my sheet1.

You should not have to manually change anything on Sheet1, but you do have to force Sheet1 to calculate. Since you are using the Worksheet_Calculate() event to trigger the code to run, something - either a user or a formula in Sheet1 - must do something that causes Sheet1 to calculate.

This site will show you various actions that will cause an Excel sheet to calculate:

https://msdn.microsoft.com/en-us/li...

If you want to code to run without an action being taken at all, you'll need to explain that requirement in a little more detail. Something has to trigger any VBA code to run, it can't actually run "automatically", i.e as if by magic.

Now let's look at this statement:

to understand why this code in sheet1 doesn't send the email automatically as supposed to do (the only thing it does is to put Sent in column N without sending the email.

Since the code works fine for me, the best I can do is explain how it should work. Why it doesn't work for you would be hard to determine from where I am sitting.

First, you may already know this, but as written, the code will only Display the email, not actually Send it, based on this line of code:

.Display ' or use .Send

.Display is the active instruction. The "or use .Send" is a Comment since it is preceded by an apostrophe.

You would need to eliminate (or comment out) the .Display instruction and use .Send to actually send the email(s).

In addition, the code requires 2 conditions that must be met in order for the email(s) to be sent:

1 - M15 (and/or M16) must contain a value > 200 based on these 2 lines of code:

MyLimit = 200

and

If .Value > MyLimit Then

2 - N15 (and/or N16) must contain Not Sent based on these 2 lines of code:

NotSentMsg = "Not Sent"

and

If .Offset(0, 1).Value = NotSentMsg Then

When I set up Sheet1 such that those 2 conditions are met - and then force a calculation - the emails are Displayed or Sent, depending on which instruction I use.

Have you tried using Breakpoints and Single Stepping through both pieces of code to see if you can figure out why it doesn't work you?

Finally, Assessement is spelled Assessment.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 17, 2017 at 03:56:47
Thank you Derby for your detailed answer. I have changed my limit at 100 and after I have done my Waterlow assessment this is what I get in row 15:
H I J K L M N
Row 15 17/10/2017 13 Medium Risk 16/03/2018 Valid 150 Not numeric

As you can see near 150 days it says not numeric. Only when I modify my the date 16/03/2018 manually not numeric string turn in Sent and accordingly will send the email.

I don't really understand why is this. Please can you still help me with that?
Many thanks for your patience.

Warmest Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#3
October 17, 2017 at 05:41:29
Please keep in mind that I can not see your worksheet from where I am sitting nor do I know your work process.

You did not include any Column letters or row numbers, so I am left to assume that the "150" is in M15. What I don't know is how the 150 got into that cell or how the dates got into the other cells, etc. You need to provide more details before we can offer any more assistance.

You also have not edited or re-posted your VBA code as I requested.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 17, 2017 at 06:42:59
Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = 100

'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("M15:M16")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook1(FormulaCell)
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell

ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description

End Sub

message edited by TOMMASO


Reply ↓  Report •

#5
October 17, 2017 at 06:55:37
Sorry I have tried to put the indentation but it doesn't come up. Apologies.

You are right 150 is M150 and it is generated by this formula: =IF(ISBLANK(K15),"",MAX(K15-TODAY(),0)) when I populate column K with a date that comes from a button in worksheet 2. With the same button in worksheet2 by clicking I populate also the other columns:

H with the current date

I with the score

J with the grade of risk

K as next due date for the assessment

I think that by doing so Worksheet 1 doesn't recognize any changes that can execute the email macro. That's why the macro it's only executed when I change manually the date in column K.

I hope this explanation can clarify more better where I am now.

Please do not hesitate to ask me any other explenation.

Thanks for you time really apprecieted and your patience.

Kind Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#6
October 17, 2017 at 07:33:15
re: I have tried to put the indentation but it doesn't come up sorry. I have tried my best. I am really new to this site.

Above the reply box you should see a tag labeled pre. If you click that button the pre tags will appear in your post. Paste the indented code between those tags and the indentations will hold. The pre tags will look similar to the following line, but without the space before and after the < and >. I can't show you the exact tags because the system will try to use them. Just eliminate the spaces. You can even type the tags in manually, just leave out the spaces.

< pre> code goes here < /pre >

re: 150 is M150 it is right

Don't you mean 150 is in M15?

re: when I populate column K from a worksheet 2 by clicking a button.

I assume (and I hate to assume) that the button runs a macro. If you include an instruction at the end of that macro to force the sheet to calculate, then the Worksheet_Calculate() code should run.

However, I don't quite understand the situation related to the "Not Numeric" entry in N15 assuming that M15 actually contains 150. Does M15 contain 150 as soon as you populate Column K via the macro or only after you manually enter the date?

I'll ask this question again and an answer would really, really, help:

Have you tried using Breakpoints and Single Stepping through both pieces of code to see if you can figure out why it doesn't work you?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
October 17, 2017 at 07:51:53
Private Sub Worksheet_Calculate()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

NotSentMsg = "Not Sent"
SentMsg = "Sent"

'Above the MyLimit value it will run the macro
MyLimit = 100

'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("M15:M16")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells

With FormulaCell

If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
 
 Else
If .Value > MyLimit Then
MyMsg = SentMsg
 
 If .Offset(0, 1).Value = NotSentMsg Then
 Call Mail_with_outlook1(FormulaCell)
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
  Application.EnableEvents = True
 End With
 Next FormulaCell

ExitMacro:

 Exit Sub

EndMacro:

 Application.EnableEvents = True

MsgBox "Some Error occurred." _
 & vbLf & Err.Number _
 & vbLf & Err.Description

End Sub

message edited by TOMMASO


Reply ↓  Report •

#8
October 17, 2017 at 08:04:20
Thank you for your reply.

M15 contains 150 as value. Sorry about the mistake. Button click run a macro that will populate immediatly column K which is linked by this fomula =IF(ISBLANK(K15),"",MAX(K15-TODAY(),0)) to column M15. The result of the formula is a subtraction that gives 150 or other values depending on the risk value.

Here it is the code related to button click that populate column K:

Case "Low Risk": rng.Offset(0, 2).Value = Date + 180


Case "Medium Risk": rng.Offset(0, 2).Value = Date + 150
Case "High Risk": rng.Offset(0, 2).Value = Date + 120

How please can I link Worksheet_calculate to button_click event?

I hope it may help.

Many thanks
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#9
October 17, 2017 at 09:39:35
I have asked the following question twice and you have still not answered it. Until you do, I can offer no further assistance:

Have you tried using Breakpoints and Single Stepping through both pieces of code to see if you can figure out why it doesn't work you?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#10
October 17, 2017 at 10:08:17
Good evening Derby,

Sorry about that. I've done it with the breaking point and I went through any piece of code. The code executes alright. The are no errors coming up.

I think that calculate worksheet in sheet 1 doesn't execute the macro mail as it doesn't recognize the formula value in M15 populated from Worksheet 2 by button click.

Kind Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#11
October 17, 2017 at 11:23:57
After the sheet has been populated by the "button click macro" (which I may need to eventually see) try pressing F9 to force the sheet to Calculate.

Again, before you press F9 you need to ensure that both conditions required by the calculate macro must be met:

M15 > 100
N15 = "Not Sent"

(I still don't understand why you can't get the pre tags to work. It's a very simple process. Click the button that says pre and paste the code between the ><)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#12
October 17, 2017 at 13:38:38
Good evening Derby,

I have forced the calculation with F9 and it works. It sends the email without problems.

Do you think could it work? How can I introduce this step in my code? Could you please help me?

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 100

    'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("M15:M16")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1(FormulaCell)
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
    Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub

Many thanks for your patience really appreciated.
Kind Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#13
October 17, 2017 at 15:06:31
In order for the Worksheet_Calculate() macro to run, the sheet must calculate. If the macro that poulates Column K is not causing the sheet to calculate, you will need to force it to calculate. However, you cannot do that inside the Worksheet_Calculate() macro because that code will only run after the worksheet calculates.

In the macro that populates Column K, put these 2 instructions as the last instructions before the code exits - after Column K is populated. I used Sheet1 in this example, you should should refer to the proper sheet in your workbook.

Sheets(1).EnableCalculation = False
Sheets(1).EnableCalculation = True

Whenever the Calculation property is Enabled, Excel will calculate the sheet. Therefore, if you Disable Calculation and then immediately Enable it, the sheet will calculate and the Worksheet_Calculate() code will fire.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#14
October 18, 2017 at 08:16:09
Good afternoon Derby,

thanks for all your time spent behind this code. Unfortunately I have tried to execute the code by forcing the calculation at work:

Sheets(1).EnableCalculation = False
Sheets(1).EnableCalculation = True

               H                 I             J           K            L           M       N
15            18/10/2017	13	Medium Risk	17/03/2018	Valid	   150	Not numeric 

And as soon I populate K16 row 15 turns in this:

15          18/10/2017         13  	 Medium Risk	 17/03/2018	Valid	   150     Sent 

due to the calculation forcing code but it doesn't send the email. Unfortunatel rather then going to Not Sent to Sent it goes to Not numeric to sent. This is why doen't send the email being Not Sent message a condition for sendind the email.

Sorry to be nuicence.

Kind Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#15
October 18, 2017 at 09:45:36
I'll tell you what I just tried, which appears to have worked perfectly:

- I put the Worksheet_Calculate() code from your original post in the Sheet module for Sheet1
- I put the Mail_with_outlook1(FormulaCell As Range) code from your original post in a standard VBA module
- I put "Not Sent" in Sheet1!N15
- I put this formula in Sheet1!M15:

=IF(ISBLANK(K15),"",MAX(K15-TODAY(),0))

- I created my own macro to "populate" Column K with a date:

Sub Populate_K15()
 Sheet(1).Range("K15") = "1/1/2019"
End Sub

As soon as I ran the Populate_K15 code, M15 showed 440, N15 changed to Sent and the email to tom@something.abc was displayed. I did not have to force calculation either manually or via code. Everything worked just as expected.

The only piece that you have not shared is the code that populates Column K. Since I apparently have everything else (2 of the 3 pieces of code, the required formula and the required sheet set up) plus my own piece of code that populates Column K, I can only speculate that the code that you are using to populate Column K is the problem.

Since you haven't shared that code with us, there is not much else I can try.

If you want to post the code that populates Column K, that might help. If you want upload the entire workbook to zippyshare.com and then post the link back here in the forum, I can take a look at. Just make sure that anything that you upload or post does not contain any personal or confidential information.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#16
October 18, 2017 at 11:11:27
Good evening Derby,

thanks a lot for helping me with this.

here you go the link:

<a href="http://www34.zippyshare.com/v/ZBGbf1sG/file.html" target

This file is a working progress code. I am a coding amateur as I am a nurse.

Thanks for looking in that.

Kind Regards
Tomamso

message edited by TOMMASO


Reply ↓  Report •

#17
October 18, 2017 at 11:54:06
Thanks for the link. I will not be able to look at the workbook until this evening, EST. I'll let you know what I find out.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#18
October 18, 2017 at 18:29:54
You need to explain to me how to use the workbook. There is much more going on than "clicking a button to populate Column K".

You said:
"Here it is the code related to button click that populate column K:

Case "Low Risk": rng.Offset(0, 2).Value = Date + 180
Case "Medium Risk": rng.Offset(0, 2).Value = Date + 150
Case "High Risk": rng.Offset(0, 2).Value = Date + 120

Those Case instructions are in a Worksheet_Change macro, not a button click macro. I really don't want to spend the time trying to figure out the chain of interconnected macros that eventually leads to Column K getting populated.

Please explain what button is used and how a user would use it.

Keep in mind that the volunteers in this forum are performing free work to help people like you who are getting paid to do what you do. It would be nice if you made it as easy as possible for us to help you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#19
October 18, 2017 at 22:47:30
Good morning Derby,

Thanks a lot for your reply and thanks for your time.

When you go on for i.e. Waterlow (Sheet2) and you click submit button you populate row 15 in sheet 1 (Summary patient chart) in Waterlow assessment box. The other tabs are a replica of the Sheet2 (Waterlow) and are linked to Sheet1 by means of a button click as you can see in Module summary.

To populate column K in in Waterlow assessment box in Sheet1 (summary patient chart) I use a Vlookup macro that sees the grade of risk in Waterlow Sheet2 at the end of table and on the base of the risk will add in column K
120 days if High risk
150 days if Medium risk
180 days if Low risk


This event is linked to submit button click in sheet2 (Waterlow) by an event change.
As soon you populate row 15 in sheet1 (summary patient chart) with submit button click in sheet 2 (Waterlow) column k is populated due to the event changed.

What I think is the formula in column M is not recognized and because of this it doesn't fire the email macro.

Please do hesitate to ask me more questions if I was not so clear.

Thanks a lot for all your excellent work.

Kind Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#20
October 19, 2017 at 16:27:48
✔ Best Answer
Thanks for the instructions. Unfortunately, there is nothing for me to suggest because either I am not seeing any problem or perhaps I don't understand the problem. Here is what I did:

1 - Since one of the criteria required for the Worksheet_Calculate() event code to call the email macro is that the cells in Summary Column N contain Not Sent, I decided to populate Summary N16:N38 with Not Sent.

2 - Since there is a Worksheet_Change() and Worksheet_Calculate() macro associated with that sheet, I disabled them in order to manually change N16:N38 and populated Summary N16:N38 with Not Sent so that I had this:

             N
15      Not Sent
16      Not Sent
17      Not Sent
18      Not Sent
19      Not Sent
20      Not Sent
21      Not Sent
22      Not Sent
23      Not Sent
24      Not Sent
25      Not Sent
26      Not Sent
27      Not Sent
28      Not Sent
29      Not Sent
30      Not Sent
31      Not Sent
32      Not Sent
33      Not Sent
34      Not Sent
35      Not Sent
36      Not Sent
37      Not Sent
38      Not Sent

3 - I then re-enabled the Worksheet_Change() and Worksheet_Calculate() macros and clicked the Submit button on the Waterlow sheet. 8 emails were created. Back in the Summary sheet I saw the following.

          N
15      Sent
16      Sent
17      Sent
18      Sent
19      Not Sent
20      Sent
21      Not numeric
22      Not numeric
23      Not numeric
24      Not Sent
25      Sent
26      Sent
27      Not numeric
28      Not numeric
29      Not Sent
30      Sent
31      Not numeric
32      Not numeric
33      Not numeric
34      Not Sent
35      Not Sent
36      Not numeric
37      Not numeric
38      Not numeric

8 occurrences of Sent means that 8 emails should have be created, right? That's what I got.

Am I missing something?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#21
October 20, 2017 at 02:04:32
Dear Derby,

thank you so much to look into these details. Now the email is fired and it works. Can you please
help me with this little bit, the email content or strbody?

 strbody = "Dear Nurse B" & " " & Sheet1.Range("H12") & Cells(FormulaCell.Row, "O").Value & " " & _
           "is due to expire in" & " " & Cells(FormulaCell.Row, "M").Value & " days" & vbNewLine & _
           vbNewLine & vbNewLine & "Thanks a lot"

This is the way it pops up:

"Dear Nurse B Patient Name is due to expire in days

Thanks a lot"

and it skips:

Cells(FormulaCell.Row, "O").Value 
where is written Waterlow assessment

Cells(FormulaCell.Row, "M").Value 
where is recorded the number of days

Do you think is an issue that can be sort it out?

Anyway it does perfectly when I manually write "Not Sent", no problem at all with the body email string.

Many thanks for your wounderful help.

You have done an amazing job and I am glad that you have shown such patience towords my project. I am sorry that I have you caused some problems. Apologies.

Kind Regards
Tommaso


message edited by TOMMASO


Reply ↓  Report •

#22
October 20, 2017 at 06:08:31
I will not be able to work on this until this evening or tomorrow morning, EST.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#23
October 20, 2017 at 06:12:30
That's fine. No problem Derby.

Thank you ever so much.

Kind Regards
Tommaso


Reply ↓  Report •

#24
October 21, 2017 at 12:39:42
VBA is focused on the wrong sheet when building the strbody string. It is focused on the sheet in which the Submit button was pressed. Therefore, strbody is being built with cells that are empty.

Add this instruction right before the strbody build instruction and you'll see what I mean.

MsgBox ActiveSheet.Name

If you refer directly to the sheet that you are trying to pull the data from, it works fine.

     
strbody = "Dear Nurse B" & " " & Sheet1.Range("H12") & _
           Sheet1.Cells(FormulaCell.Row, "O").Value & " " & _
          "is due to expire in" & " " & _
           Sheet1.Cells(FormulaCell.Row, "M").Value & _
          " days" & vbNewLine & vbNewLine & vbNewLine & "Thanks a lot"

You might consider writing the instruction as follows so that there is no confusion as to which sheet you are referring to:

With Sheets("Summary Patient Chart ") '<-----Note the space!
     strbody = "Dear Nurse B" & " " & .Range("H12") & _
                .Cells(FormulaCell.Row, "O").Value & " " & _
               "is due to expire in" & " " & _
                .Cells(FormulaCell.Row, "M").Value & _
               " days" & vbNewLine & vbNewLine & vbNewLine & "Thanks a lot"
End With

BTW: Do you notice the space after the word Chart in the sheet name? That is there because you have a trailing space in the sheet name in your workbook. Unless you need that space for some reason, I suggest that you delete it. It's sloppy and only causes confusion.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#25
October 23, 2017 at 02:04:42
Thank you so much Derby for your wonderful job in helping me to sort this matter out.

Thanks a lot for you time spent on this. I know that we live in a frantic world where it is not so easy spare some time for others.

I really appreciate what you have done. Now the project works amazingly and I am sure it could be beneficial to my patients.

As I have told you I am nurse and I like to help people to make their life as simple as possible.

I carry out my job as Jehovah's witness volunteer. I long for the time when as fortold in Isaiah 33:24 "No Resident Will Say: ‘I Am Sick’"

In the main time I try my best to help people in needs. Thank you for contribuiting to this.

If you want to know more about Jehovah's witnesses beliefs please visit our website:

www.jw.org 

Kind Regards
Tommaso

message edited by TOMMASO


Reply ↓  Report •

#26
October 23, 2017 at 03:51:36
If you are satisfied that it is working correctly, please chose a Best Answer so that the thread will be marked as Solved.

If you have any further questions, please start a new thread.

Good luck with your work.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question