PDA

View Full Version : 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:hi:
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 ;)

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
Charlize
01-25-2008, 02:10 AM
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:hi: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
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:

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

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 :p

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