Outlook macro to check a value of a cell in an attachment and send an email based on that value

gmayor · #1 03-24-2015, 07:57 AM

The macro should only leave Excel open if it was open before running the process.
The subscript out of range error suggests that VBA has crashed irrecoverably (in which case you need to reboot) or the criteria set at the top of the macro to match your data i.e.

Code:

Const strWorkBook As String = "Count0To12Hr.xls"        'The name of the attached workbook
Const strSheet As String = "Count0To12Hr"        'The name of the worksheet to process
Const strCell As String = "E2"        'The cell to process
Const iCount As Integer = 19        'The threshold value of the above cell
Const strMessage As String = "This is the reply message body text." 'The default signature will be included.

What is the range of values that could appear in E2?
What is the range of values that you might put in iCount?
Look up the numeric ranges for Integer and Long variables.

It might be better to change

Const iCount As Integer = 19
to
Const iCount As Long = 19

ketangarg86 · #2 03-24-2015, 08:56 AM

I have two reports running Count0To12Hr and Count12To24Hr both we the similar macros. Count0To12Hr works fine but Count12To24Hr still gives the same error - Subscript out of error.

What is the range of values that could appear in E2? -100 to 100
What is the range of values that you might put in iCount? -100 to 100

Now, I tried the scenario where iCount is -15 and the cell value is -10. It gives me an error at the below
olAttach.SaveAsFile tmpPath

Could you please advise.

ketangarg86 · #3 03-24-2015, 11:23 AM

Could it be the case that in my excel sheet the cell value is formatted as big decimal and in my outlook vba I am using long/integer. Unfortunately, I cannot change the excel attachment format.

Could this be the reason for subscript out of range error and macro not running properly.

If the excel cell value = -0.2 and iCount value as -15 (this works fine)
If the excel cell value = -10.2 and iCount value as -15 (this gives subscript error)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create macro program to automatically email outlook with a visible cell range	BillMaintenance	Excel Programming	19	03-11-2015 03:19 PM
Can anyone here tweek this macro for renaming Excel files based on a cell's contents?	chrisd2000	Excel Programming	6	07-01-2014 01:53 PM
Email alert based on value in cell	sweetcuda	Excel	0	03-12-2014 12:44 PM
Command button - save in temp folder and send email with attachment	bigbird69	Word VBA	13	11-18-2012 10:06 PM
Macro based on cell value	ubns	Excel Programming	1	05-07-2012 04:03 AM