PDA

View Full Version : Solved: Have a timer in code to sent out a Email with a attachment



Shazam
09-20-2005, 01:16 PM
Good Afternoon everyone,

I have this code below That needs some modifications. Can this code be modified that it will automatically email the attachment Monday through Thursday. But sent it out to the Contacts that I have named "Sand Lab Data". Then on Friday I would like it sent out by the contacts "Sand Lab Data" and Anybody@hotmail.com. Also can the code have some kind of timer so everyday at 6:00 A.M in the morning, The code will run it self on that paticular time. One more thing when it sent it out as a attachment I would like in the Sunject Bar says: "Sand Lab Data"

Thank You!



Sub Send()

Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.createitem(olmailitem)
With objmail
.To = "Anybody@hotmail.com"
.cc = "SomebodyElse@hotmail.com"
.Subject = "TEST EMAIL"
.Body = "THIS IS THE BODY"
.NoAging = True
.Attachments.Add ("C:\A test.xls")
.display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True

End Sub

chocobochick
09-21-2005, 06:27 AM
First of all, you probably don't want the timer in your VBA code. Otherwise, the code would have to be running in an infinite loop, bogging down all your other processes. The best way to handle the timer is to use an automated task scheduler. You might find the Windows Scheduled Tasks program in Start/Programs/Accessories/System Tools. Then set it to run the MS Outlook program file with the /autorun command switch to run your programmed VBA macro. The command line should resemble this:

C:\Program Files\Microsoft Office\Office\Outlook.exe /autorun NameOfMyMacro

With this, you can use an If Then statement to determine the day of the week to modify your addresses accordingly. As for the addresses, there are two ways you can go about it, both of which are equally inconvenient:

From your Contacts list: The intended way for the Outlook model to handle this is to use the Recipients collection and the ResolveAll method. The code would probably look like this:


objMail.Recipients.Add("Sand Lab Data") ' Name of distribution list in Contacts
' Add Somebody on Fridays
If Weekday(Date()) = 6 Then objMail.Recipients.Add("Somebody") ' Name of somebody@hotmail.com Contact
objMail.Recipients.ResolveAll


This should collect all the addresses from your Contacts list and place it into your email. The problem with this, however, is that it probably triggers a popup box that asks if you're okay with another program accessing Outlook's email addresses, thanks to a Microsoft Security patch, and this security setting is unchangeable short of removing the patch. To work around this, you might have to install Outlook Redemption (http://www.dimastr.com/redemption/) and use the SafeMailItem object instead.

Fill in the names manually: Just alter the To property of the MailItem instead, or set the Address property of each Recipient object you add. It's a little simpler than the encumbersome workarounds above, but if you ever need to change an address, you have to change the actual code and not just the Contacts.

Also, if you already have Outlook open (which you will if you use the scheduler/autorun method above), you don't have to create a new Outlook Application. You can just create the MailItem by calling "Application.CreateItem(olMailItem)" directly.

Hope that helps!

Shazam
09-21-2005, 01:13 PM
Thank You for replying. I never thought of that by doing a scheduled task. Thanks for that input. I implemented your code so I will wait tp put resolved till Friday to see if it works. That pop up box is annoying, so I used this code below to avoid that. Thank you for the code and I will reply back on Friday.

One more thing on the Scheduled Task do I put the macro name: Send_Lab_Data or Sub Send_Sand_Lab_Data ()


Sub Send_Sand_Lab_Data()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
objmail.Recipients.Add ("Sand Lab Data") ' Name of distribution list in Contacts 'Add Somebody on Fridays
If Weekday(Date) = 6 Then objmail.Recipients.Add ("anybody@nfco.com (janybody@nfco.com)") 'Name of somebody@hotmail.com Contact
objmail.Recipients.ResolveAll
.Subject = "Sand Lab Data"
.Body = ""
.NoAging = True
.Attachments.Add "S:\Sand Lab Data\Pollohuesos_recalc1.xls"
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True
End Sub


I posted a thread couple of days ago and got no response can you take a look at the link to see this is possible or not ? Thanks!
http://vbaexpress.com/forum/showthread.php?t=5250

chocobochick
09-21-2005, 01:27 PM
Just "Send_Sand_Lab_Data". Only Sub procedures with no arguments can be used as macros, I believe. Also, you can probably change your system's date and time to Friday morning, test your scheduled task (though I recommend testing it on your own email address), and then change your date back.

Shazam
09-22-2005, 07:48 AM
The Sheduled Task say: Could not start.
I put this command line:
"C:\Program Files\Microsoft Office\Office\OUTLOOK.EXE / autorun "Send_Sand_Lab_Data"

I put the code in the Microsoft Outlook vb module.

Am I missing something?

chocobochick
09-26-2005, 05:41 AM
You probably need to rearrange the quotes and remove the space before autorun. Try it like this:
"C:\Program Files\Microsoft Office\Office\OUTLOOK.EXE" /autorun Send_Sand_Lab_Data

Shazam
09-26-2005, 10:37 AM
Hi chocobochick,

Thank You for posting back. I copy your command line and use it now it gives me a different message.
It says:

The Command line argument is not valid. Verify to switch you are using.

Doing you know what that means?

chocobochick
09-26-2005, 12:52 PM
/sigh

Figures. I've looked it up, and it looks like the /autorun switch just became available in Outlook 2003. http://vbaexpress.com/forum/images/smilies/banghead.gif

At this point, I don't think there's any pretty way to go about it now. If you wanted to run your own timer within the macro, remember that it's really easy to inadvertently call the procedure for a second instance while the first instance is still running. This means you might accidentally send multiple emails, which could annoy your recipients or even get you reported for spamming. I'd recommend keeping the macro we tried to /autorun above, but assign it to a button that you'll just have to press every morning.

If you feel the need to try it anyway, use the Sleep API like this:


Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub EmailTimer()
Dim sendTime as Date
Dim s as Long
sendTime = TimeValue("6:00:00") ' 6:00 AM
Do While True
s = DateDiff("s", Time(), sendTime)
If s < 0 Then s = s + 86400 ' Add 24 hours to negative time difference
Sleep(s*1000)
SendEmails() ' Call sub procedure to create and send emails
Loop
End Sub

Shazam
09-26-2005, 01:14 PM
Ok that make sense. I have Microsoft Office 2000. I will upgrade. Thanks for the other option But I will update my Microsoft Office to 2003. Thank You very much.