Outlook Help

**sindhuja** · 01-20-2008, 08:37 PM

Hi All,

Is it possible to sent e-mail via outlook to different users depending upon the system time. The system time to be compared with the specific column named "Deadlines" in excel and e-mail to be sent to concern person specified in the TO column of that row.

Before sending e-mail to the concern person all incoming mails to be checked whether e-mail sent to that particular deadline. If sent then the time of mail sent is to be documented in the column named "Time mail sent".

If not, alert to be sent and also, this is to be notified to the higher officials that "e-mail has been sent to the concerned person as job to be done by that person has missed the deadlines".

This is the urgent requirement.
Thanks for all your help in advance !!!!

Regards,
Sindhuja

**sindhuja** · 01-21-2008, 07:07 AM

Hi All,

Did anyone get a chance to look into my requirement...

Regards,
Sindhuja

**sindhuja** · 01-22-2008, 07:21 PM

Hi All,

I am struck up with this issue...
Please anyone help me out !!!

Regards,
Sindhuja

**killr** · 01-24-2008, 03:08 AM

Funny .. I was looking for something simular...

This is what I have for sending email. You just have to adjust thing to your needs

[vba]Dim objOl As Outlook.Application
Dim objMail As Object

Set objOl = Outlook.Application 'Start Outlook
Set objMail = objOl.CreateItem(olMailItem)
'Creates an emailmessage. olMailItem can be replaced with 0 (code for emailitem)

'////////////////////////////////////////////////////
'This is for Outlook 2007. For determining the sender.
'The Microsoft Outlook 12 Object Library has to be activated/referenced to be able to use this
'////////////////////////////////////////////////////
Dim objAccount As Outlook.Account
For Each objAccount In objOl.Session.Accounts
If objAccount.DisplayName = "Name Outlook-account" Then
Set objMail.SendUsingAccount = objAccount
End If
Next
Set objAccount = Nothing
'////////////////////////////////////////////////////
'You might want to use this option for older versions of Outlook
'where an email is sent on behalf of another emailadress
objMail.SentOnBehalfOfName = "Name sender <sender@email.com>"
'////////////////////////////////////////////////////

With objMail
.To = "to@email.com" 'Adjust this to your needs
.CC = "cc@email.com" 'Adjust this to your needs or leave it out
.BCC = "bcc@email.com" 'Adjust this to your needs or leave it out
'It's not possible to adjust the from field. See solution for Outlook 2007 above
.Subject = "Subject of the e-mail" 'Adjust this to your needs
.Body = "This is the place to put the content of the message"
'.HTMLBody = "<HTML><P>TEST</P></HTML>"
.NoAging = True
.Attachments.Add "C:\WINDOWS\WIN.INI" 'The location of the attachement.
'.Display 'Displays the email before it is sent.
'.Save 'Prevents the question to save the message, saves it in the inbox.
.Send 'Sends the message.
End With

Set objMail = Nothing
objOl.Quit
Set objOl = Nothing[/vba]

**Charlize** · 01-25-2008, 02:10 AM

Originally Posted by sindhuja

Hi All,

Is it possible to sent e-mail via outlook to different users depending upon the system time. The system time to be compared with the specific column named "Deadlines" in excel and e-mail to be sent to concern person specified in the TO column of that row.

Before sending e-mail to the concern person all incoming mails to be checked whether e-mail sent to that particular deadline. If sent then the time of mail sent is to be documented in the column named "Time mail sent".

If not, alert to be sent and also, this is to be notified to the higher officials that "e-mail has been sent to the concerned person as job to be done by that person has missed the deadlines".

This is the urgent requirement.
Thanks for all your help in advance !!!!

Regards,
Sindhuja

Give a sample layout with some data in excel.
Some tips :
1. You need to use the on time event to check the time in the column. Start this when you open the workbook.
2. For sending mail without warning messages you could use cdo
3. When you send an email, check the mailsent column. If something is filled in, don't send the mail.

It's possible to create something. What have you tried already ...

Charlize

**sindhuja** · 01-27-2008, 07:08 AM

Hi Charlize,

I am unable to attend the file which will help for better understanding...

-Sindhuja

**killr** · 01-28-2008, 01:26 AM

Originally Posted by sindhuja

Hi Charlize,

I am unable to attend the file which will help for better understanding...

-Sindhuja

This is the code I use & it works fine for me:

[vba]Sub Mail()
Dim DeadLine As Date
Dim SysTime As Date
Dim PersResp As String
Dim TimeSent As Date
Dim HighOfficial As String
Dim i As Integer
Dim objOl As Outlook.Application
Dim objMail As Object

For i = 2 To 6
DeadLine = Sheet1.Range("A" & i)
SysTime = Sheet1.Range("B" & i)
PersResp = Sheet1.Range("C" & i)
TimeSent = Sheet1.Range("D" & i)
HighOfficial = Sheet1.Range("E" & i)
If TimeSent <> "0:00:00" Then
ElseIf SysTime > DeadLine Then
Set objOl = Outlook.Application 'Start Outlook
Set objMail = objOl.CreateItem(olMailItem) 'Creates an emailmessage. olMailItem can be replaced with 0 (code for emailitem)
With objMail
.To = PersResp 'Adjust this to your needs
.CC = HighOfficial 'Adjust this to your needs or leave it out
'.BCC = "bcc@email.net" 'Adjust this to your needs or leave it out
.Subject = "Project overdue" 'Adjust this to your needs
.Body = "You've past the deadline for your project. Please report about the status of your project."
'.HTMLBody = "<HTML><P>TEST</P></HTML>"
.NoAging = True
'.Attachments.Add "C:\WINDOWS\WIN.INI" 'The location of the attachement.
'.Display 'Displays the email before it is sent.
'.Save 'Prevents the question to save the message, saves it in the inbox.
.Send 'Sends the message
End With
Set objMail = Nothing
Set objOl = Nothing
Sheet1.Range("D" & i).Formula = SysTime
Else
End If
Next i
End Sub[/vba]

As you can see, the deadline is in Column A. I've inserted an extra column B with the system time ( using the formula "=now()" )

The only thing I have yet to implement is, counting the rows with data, so that the integer "i" is always long enough to pass through all the rows, but that won't be too much of a problem..

You can then use one of the events to run this macro..

Anyways, if you could tell us which data is in which columns, we just might be able to assist you further with your missoin

Do let me know if this helped you in any way...

**sindhuja** · 02-02-2008, 04:19 AM

Hi Killr,

Thanks for the help !!

The problem now is, i am unable to attach the files i am currently using for better understanding..

Let me try this and let you know the problem..

-Sindhuja

**sindhuja** · 02-09-2008, 09:18 PM

Hi All,

I have attached the sample file for the better understanding.

Column named SUBJECT - Every incoming mail needs to be checked for the subject. The subject of e-mail received to be compared with the SUBJECT Column. If same, then time to be noted in the INCOMING MAIL TIME column.

Column named DEADLINE - The data in DEADLINE column to be compared with the SYSTEMTIME column and the INCOMING MAIL TIME column.
If INCOMING MAIL TIME is not captured, then mail to be sent to the concern person specified in the TO, CC columns 15minutes prior to the DEADLINES column value.

For ex, deadline is 10.00, and if incoming mail time is nil then mail to be automatically sent by 9.45...
if deadline is 10.00, and incoming mail time is 9.30 need not send mail.

Hope this helps out...

-sindhuja

**sindhuja** · 02-18-2008, 01:07 AM

Hi All,

Please help me out in this as this is very urgent..
Attached the sample file for the reference

-Sindhuja...

**sindhuja** · 04-16-2008, 11:46 PM

Hello All,

Please help me out in this, eagerly waiting for the results...

Immediate help will be highly appreciated. I dont know how to correlate outlook and excel...

-Sindhuja

**sindhuja** · 05-11-2008, 06:23 AM

Help me out in this...
Annyone please.....

-Sindhuja

**sindhuja** · 09-22-2008, 05:06 PM

Is there anyone to help me out in this....
Am still struggling with this...

-Sindhuja

Thread: Outlook Help

Thread Tools

Display

Outlook Help

Posting Permissions