Need Excel to send Automatically an Email

Hi guys,

I need a macro that automatically sends an outlook email every Friday of the week at 5pm.

For example,

Column A contains date like Late and On Time, if its Late, it will send an email.

It need to refer to column B to look for the person to email.

The column B contains codes or text, like 1,2, etc. Each code corresponds to different emailing list like for example 1 = [email protected], 2 = [email protected] and so on.

Can anyone help me? This is too complicated for me, since it will be using the outlook.

The message in the email should be this “ You have a late delivery. Please refer to the delivery file for more details.”

Thanks! This is a good practice for some since this is something new I think.

This is nothing terribly new under the sun. Give this web site a look to help
get you started...

http://www.rondebruin.nl/sendmail.htm

Once you have worked out how to send the emails then look at using

Application.Ontime

to do the e-mailing...
--
HTH...

Jim Thomlinson


"japorms" wrote:

>
> Hi guys,
>
> I need a macro that automatically sends an outlook email every Friday
> of the week at 5pm.
>
> For example,
>
> Column A contains date like Late and On Time, if its Late, it will send
> an email.
>
> It need to refer to column B to look for the person to email.
>
> The column B contains codes or text, like 1,2, etc. Each code
> corresponds to different emailing list like for example 1 =
> [email protected], 2 = [email protected] and so on.
>
> Can anyone help me? This is too complicated for me, since it will be
> using the outlook.
>
> The message in the email should be this “ You have a late delivery.
> Please refer to the delivery file for more details.�
>
> Thanks! This is a good practice for some since this is something new I
> think.
>
>
> --
> japorms
> ------------------------------------------------------------------------
> japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
> View this thread: http://www.excelforum.com/showthread...hreadid=572372
>
>

Guys,

Can someone check my code. I've made some changes but its not working.

Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Sheets("2006").Columns("V").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 9).Value) = "Late" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & cell.Offset(0, -19).Value & vbNewLine & vbNewLine & _
"Please contact us to discuss bringing your account up to date" & vbNewLine & vbNewLine & _
"Please refer to U-order number" & cell.Offset(0, -2).Value & vbNewLine & vbNewLine & _
"This an automated generated email, you don't need to reply"
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

guys,

little help, please

i'm going crazy thinking about this.

alright, i check and edited the original code and it seems that the code

only allows the email once in a day. the problem is, i can't decipher which of the code does that.

does anyone knows about this?

after hours and hours of testing and analysis

I've found the flaw in my code. It's all because to "Lcase". This syntax can't read string in upper and lower case. So I just change the reference data in all Upper Case since I can't think of a turn around solution.

Thanks to you all.

Hi japorms,

Can you please forward me the excel file for reference. I have the same kind of requirement. Your file should help me a lot. :-)

Thanks...