Adding a comment to Conditional Formatting via VBA

[ctbanker]

Hi All,

I'm very new to the VBA world and don't have much experience with coding. I want to do the following, but am not sure how to:

I have a column called "Classification", and if there is any text (example "Fee" or "Due Diligence") in a cell, I want a comment ("please fill either choose Loan Proceeds or Prepaid Deposit") to pop-up at the corresponding cell under "Paid From". I have Conditional Formatting set-up to make the cell red when the corresponding "Classification" cell is occupied, but that doesn't clearly state what I need done (and isn't as cool either).

Any help would be greatly appreciated! Please refer to a sample worksheet below...

Fees Breakdown
Classification	Paid From:	Item	Disposition	Amount
Fee	Loan Proceeds	SBA Guarantee Fee	Bank Controlled Account	94,256.50
Due Diligence	Prepaid Deposit	Appraisal	General Ledger Account	12,000.00
Fee	Loan Proceeds	UCC Search / Filing Fee	Bank Controlled Account	120.00
Due Diligence	Prepaid Deposit	Environmental	General Ledger Account	3,900.00
Fee	Loan Proceeds	SBA Loan Packaging Fee	Bank Controlled Account	2,500.00
Fee	Prepaid Deposit	Flood Determination Fee	Bank Controlled Account	67.50
Fee	Loan Proceeds	Loan Closing Fee	Customer Refund	6,569.00
Fee	COMMENT HERE			499.75
			Total	119,912.75

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[cooper645]

shouldn't require inserting, the bit "SendEmail" should look for that macro name, so no need to merge them.
also dont forget to adjust the time to when it should automate, and bear in mind excel needs to be open, maybe set a windows task schedule???

 

[cooper645]

possibly try, but cant say whether it will or wont work



<code style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; line-height: 14px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-size: 13px; background-color: rgb(238, 238, 238); white-space: inherit; ">Sub AutoSend()
If Weekday(Now, vbMonday) < 6 Then 'check if weekday is < 6, starting by Monday with 1 (Sat = 6, Sun = 7)
Application.OnTime TimeValue("09:45:00"), ActiveWorkbook.SendMail Recipients:="abc@email.com", Subject:="Subject header" & Format(Date, "dd/mmm/yy")
End If
End Sub</code>

 

[ctbanker]

I tried the last code you sent... when I try to run it, it pops up with a message saying "Compile Error: Syntax Error" for the second line of code.

I'm going to try to keep the two codes separate and run that as well.

As for windows task schedule, I tried that yesterday but that wouldn't work either. I may have set it up incorrectly. Thanks so much for your continued help!

 

[ctbanker]

It worked when I kept the codes separate!!! So the way around not having to keep excel open is setting up windows task schedule??

Any way around the message which reads "A program is trying to send an e-mail message on your behalf. If this is unexpected,..."

 

[cooper645]

Not sure at the moment. Let me have a look into it for you. I'll get back with some answers ref task schedule and message.

 

[cooper645]

A good guide to task scheduler here;

Task Scheduler - Create New Task - Windows 7 Help Forums

Think this should do the job for the warning window, gimme a sec, I'll fire up the PC and pop it in a full code

OlSecurityManager.ConnectTo OutlookApp

OlSecurityManager.DisableOOMWarnings = True

On Error Goto Finally

'... any action with protected objects ...

Finally:

OlSecurityManager.DisableOOMWarnings = False

<tbody>
</tbody>

 

[cooper645]

[FONT=Arial][SIZE=2]Sub SendEmail_CancelWarning()
[/SIZE][/FONT][SIZE=2][COLOR=#333333][FONT=arial]
Dim OutlookApp As Object
Set OutlookApp = CreateObject("Outlook.Application")

OlSecurityManager.ConnectTo OutlookApp
OlSecurityManager.DisableOOMWarnings = [COLOR=blue]True
[/COLOR][COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] Goto [COLOR=blue]Finally
[/COLOR]ActiveWorkbook.SendMail _
Recipients:="abc@email.com", _
Subject:="Subject header" & Format(Date, "dd/mmm/yy")
[COLOR=blue]Finally[/COLOR]:
OlSecurityManager.DisableOOMWarnings = [COLOR=blue]False
[/COLOR]
End Sub[/FONT][/COLOR][/SIZE]

 

[cooper645]

if none of those work, i'm a bit stumped to be honest,

i would leave it as the two codes that work and try to get task scheduler to run properly.

Hope this somehow all works out

 

[ctbanker]

Thanks again! I tried using that last code but some error was popping up. I'll try getting task scheduler to work.

 

[ctbanker]

So I got Task Scheduler to work but can't bypass that security message. I tried the code above [Sub SendEmail_CancelWarning()], but I get this error: Run-time error '424': Object Required. Any idea what this may be?