Macro that will create & send standard email

[jamie.c]

Hello Everyone,

Im wondering if anyoune can help me write some code please?
I am creating a service log, that will hopefully do sum great things!!

I would like a macro that recognises the cell colour in column F if it is yellow i would like a standard email to be created & sent to the corresponding customer email address in column D. is this even possible??

Many Thanks

Jamie.c

[royUK]

Are you using Conditional Formatting to get the cell colour?

[jamie.c]

Hello Roy

Thanks for your reply, yeah im in the process of doing it that way, but if thats not going to help with the macro im open to your ideas??

Thanks in advance

Jamiec

[jamie.c]

----------bump--------------

[jamie.c]

Can Anyone help please?

Many Thanks

Jamiec

[broro183]

hi Jamie,

You may find useful info on the below links:
http://www.rondebruin.nl/sendmail.htm
especially on this link which is near the bottom of the above page...
http://www.rondebruin.nl/mail/change.htm

Ron's mention of conditional formatting is quite relevant here because conditional formatting relies on a condition being met (eg a cell value changing) & it is likely that the same logic can be applied within a macro.

hth
Rob

[jamie.c]

Thanks for your response Rob, Ive looked at the examples and I haven got enough knowledge to modify those to suit my worksheet, can anyone help please??

Many thanks in advance

Jamiec

[broro183]

hi Jamie,

Apologies for the slow response - I've been away for the weekend & now that it is time to go back to work I may be slow again...

Hopefully, if I'm too slow someone else will jump in. Can you please attach an example file in the Excel 2003 (.xls) format?

Rob

[jamie.c]

Hi rob, My opologies to for being slow!

Thanks for your response,

Ill do a swap with you and ill move out to new zealand looks a beautiful place to live!!!

Jamiec

[Leith Ross]

Hello jamie.c,

That was some challenge! I am awarding you points for this one. Columns "F" and "G" have conditional formatting applied. If the service date is 28 to 0 days before today, it is colored yellow. If today is 1 day or more past the service date, it is colored red.

Emails are sent when the command button "Send Emails" on Sheet1 is clicked. After an email is sent, the macro adds a Comment to the cell with the data and time the email was sent. If an error occurs when sending a email, the macro will stop sending emails until the error is corrected.

Conditional Formatting Formulae

Please Login or Register  to view this content.

Send Emails Macro

Please Login or Register  to view this content.

[jamie.c]

That is perfect!!!!!

Thankyou so much!!

Just a quick Question though, what email is that set up to send at the moment? can it select a signature as a template?

And how easy would it be to add some code that will automatically print a letter off that will add the relevant company name in to it, so a copy can be posted? is that easy or even possible??

Many many thanks

Jamiec

[jamie.c]

---------bump----------

[jamie.c]

Can anyone help please?

[jamie.c]

Hello All!

Just wondering if anyone out there knows if it is possible for the macro that Leith kindly wrote for me would be able to select a certain signature as the template email?

Any Help or advice would be much appreciated

Thanks in Advance

Jamiec

[wotadude]

you may find this helpful

http://www.rondebruin.nl/mail/folder3/signature.htm

[jamie.c]

Hello All

I have A some code that Leith kindly wrote for me some time ago, I never got round to finishing the project, but as I picked it back up and had a play with it I realized I didnt work as it did when I first recieved it. when I press the "Send Email" Button now it comes up with "Error - emails stopped active X component cant create object" Can anyone help me out please n point me in the right direction?

Many Thanks

jamie.c

[jamie.c]

Hello All,

It has been a while since I started this project, and it never got finished before, so I have picked it back up to have a play with it, and it seems not to work now!!!!!!! I know it did before, can anyone help me out please?? When I press the "send Email" Button It comes up with " Error - Emails stopped Active X Component cant create object" Can anyone help please?

Many Thanks

Jamie.c

[broro183]

hi Jamie,

Can you please post your latest version of your file for us to look at?

Longshot questions (without investigating the error):
Have you changed the name of "Sheet1"?
Does the computer you are using have MS Outlook installed?


Rob

[jamie.c]

Good Morning Rob,

Many thanks for your reply, Its very strange, It worked great the first time I used it then it got put to one side for a while, now It just comes up with the error, even when I go back to the original posted back to me. I have definately got Ms Outlook & I havent altered anything of the original! I cannot think what it could be

Thanks Again, look forward to your response

Jamie.c

[jamie.c]

---------BUMP---------

Any Help would be much appreciated

Thanks In advance

[Leith Ross]

Hello jamie.c,


I downloaded the workbook and it runs fine on my machine. I am running Excel 2003 under Windows XP. Has your computer software changed since the last time the program ran correctly? Are you running this on single computer or on a network?

[jamie.c]

Good afternoon Leith,

Thank you so much for your reply, much appreciated!

Its strange my computor/software has not changed, but the document is on a server, will that make a difference?

Thanks again

Jamie.c

[jamie.c]

On Error GoTo ErrorOut

Set olApp = CreateObject("Outlook.Application")

Set Wks = Worksheets("Sheet1")
Set Rng = Wks.Range("F4")
Set RngEnd = Wks.Cells(Wks.Rows.Count, Rng.Column).End(xlUp)
Set RngEnd = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd)
Set Rng = Wks.Range(Rng, RngEnd).Resize(Columnsize:=2)

Application.ScreenUpdating = False

For Each Cell In Rng
CellColor = xlColorIndexNone
With Cell.FormatConditions
Cell.Select
Condition1 = Evaluate(.Item(1).Formula1)
If Condition1 = True Then CellColor = .Item(1).Interior.ColorIndex
Condition2 = Evaluate(.Item(2).Formula1)
If Condition2 = True Then CellColor = .Item(2).Interior.ColorIndex
End With
'Yellow means due in 4 weeks. Comment is holds date time stamp for email.
If CellColor = 6 And (Cell.Comment Is Nothing) Then
SendTo = Wks.Cells(Cell.Row, "D").Text
Set olEmail = olApp.CreateItem(0)
With olEmail
.To = SendTo
.Subject = "Service Schedule"
.Body = "Email Test." 'Include your message between the quotes
.Send
Cell.AddComment "Email sent " & Now()
End With
End If
Next Cell

ErrorOut:
Application.ScreenUpdating = True

If Err <> 0 Then
MsgBox "ERROR - Emails Stopped" & vbCrLf & Err.Description & vbCrLf & SendTo
Err.Clear
On Error GoTo 0
End If

'Terminate the Outlook
olApp.Quit

'Free objects in memory
Set olApp = Nothing
Set olEmail = Nothing

End Sub

If this helps, the text in green is highlighted on the debug

Thanks again

Jamie.c

[jamie.c]

--------bump----------

[Leith Ross]

Hello jamie.c,

I don't know why you would receive the error "Component can't create object" because the object has already been created. At this point, the macro is closing Outlook, not creating it. Have you checked that you have been granted the proper access rights to create and destroy objects on your server?

[jamie.c]

Hello Leith,

Thank you for your reply,

I checked my email & server security settings, I enabled some that I thought may work but it still does the same, my outlook is 2007, that may have changed from the original, would that make a difference?

Is there a different approch that I could take to acheive the same out come?

Any help would be much appreciated

Jamie.c

[maximpinto]

Hello jamie.c,

That was some challenge! I am awarding you points for this one. Columns "F" and "G" have conditional formatting applied. If the service date is 28 to 0 days before today, it is colored yellow. If today is 1 day or more past the service date, it is colored red.

Emails are sent when the command button "Send Emails" on Sheet1 is clicked. After an email is sent, the macro adds a Comment to the cell with the data and time the email was sent. If an error occurs when sending a email, the macro will stop sending emails until the error is corrected.

Conditional Formatting Formulae

Please Login or Register  to view this content.

Send Emails Macro

Please Login or Register  to view this content.

Dear Leith,
I tried using this sheet by modifying only data but when i try to click "Send email",,my outlook only closes , y is this happening.
I am using all 2007 versions
Thanks
Max

[Leith Ross]

Hello maximpinto,

Since I don't have Office 2007, I can't really answer your question as to why. There were at lot of changes made in Office 2007. This is first time Microsoft broke with its tradition of backward compatibility. The code works fine in Office 2000 and 2003, but evidently something has changed in 2007. I suspect it is related to changes in Outlook.

You may want to defer your questions to either Sue Mosher or Ron De Bruin. Sue is the Outlook guru and Ron the email man. You can post on either or both of these Microsoft sites if you want to reach them.

http://groups.google.com/group/micro...el.programming
http://groups.google.com/group/micro...ok.program_vba