Macro

[eit09]

Hello,

I clicked on the developer tab in microsoft outlook and saw the ability to writ a macro. Unfortunatly there is no macro record button like in excell. Each week I have to attach a document (same file name just updated file) that is always located in the same loaction on my pc. I would like a write a macro to attach it this document automatically. I tried to creat a simialr situation in xcell with the record and copy it into the code but no luck. Below is what I tried to write with the code. Any one know if what I am attempting is possible?
Sub Timer()
'

ChDir "H:\Timer"
Workbooks.attach FileName:="H:\Timer\Jason-Time-Sheet.iif"
End Sub

 

[MintJulep]

Yes, it is possible.

 

[eit09]

MintJulep,

Thats great news. I write the follwing script in the vba editor but the macro doesn't run. I have never acutally stepped into the macro and typed commands I have only used the record macro xcell has. Could you take a look below and comment?

Sub Timer()
'

ChDir "H:\Timer"
Workbooks.attach FileName:="H:\Timer\Jason-Time-Sheet.iif"
End Sub

 

[MintJulep]

Your code won't do what you want.

Try something like

http://bit.ly/UjDEUu

 

[bacon4life]

There are lots of ways to automate this using either Outlook, Excel or the Windows task scheduler.

One way with Outlook would be to create a weekly reminder on the calendar, then have the reminder trigger sending out the email. This link shows how to send out emails based on calendar reminders. You should just need to add an if statement so it only sends the email for your specific weekly reminder, then add the file location in the SendPage subroutine.

http://support.microsoft.com/kb/236774

If you have both outlook and excel running, you can have excel send out an email. One drawback is that Outlook will have a dialog box requiring you to approve excel sending out an email on your behalf.

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

Another way is to download sendemail.exe from

http://caspian.dotconf.net/menu/Software/SendEmail/.

Then create a *.bat file that contains the command line parameters for sendemail.exe. Finally, using Windows task scheduler to run the *.bat file once each week. You would need to ask your IT department for the IP/Port number of your mail server as well.

*.bat file
"sendemail.exe" -f fromaddress@youcompany.com -t toaddress@youcompany.com -u "This is the subject" -m "This is the contents" -s 101.0.0.0:25

 

[yakpol]

eit,

Try this code from Excel VBA. Modify email parameters at your will.

Sub SendEmail2()

Dim myItem As Outlook.MailItem
Dim myolApp As Outlook.Application
Dim myRecipient As Recipient

Application.AutomationSecurity = msoAutomationSecurityLow


Set myolApp = CreateObject("Outlook.Application")
Set myItem = myolApp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments

myAttachments.Add "C:\MyPath\MyFile.dat"

With myItem
    .Importance = olImportanceHigh
    .FlagIcon = olBlueFlagIcon
    .ReadReceiptRequested = True
    .Subject = "You won a lottery!"
    .To = "MonikaL@whitehouse.gov"
    
    .Body = "Hello my dear..."
    .Display 'optional
    .Send
End With


End Sub

cheers,

Yakpol

 

[IDS]

A useful resource for using VBA in Outlook is:

http://www.jpsoftwaretech.com/category/outlook/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[eit09]

@ YAKPOl

Your Code worked great the only thing I need to tweak now is to keep my signature message (contact info) that normally is applied whenever I create a new email. Do I need to add something before the attachment portion of the code to start by opening a new email?

For the code to run I had to delete two code lines "Application.automationsecurity = msoautomationsecuritylow" and ".readreceiptrequested = true" Do I need to be aware of anything by deleting these two lines?

Thanks again!

 

[yakpol]

eit,

To add your signature you can add a string to the myItem.body

 strMessage = "Hello dear..."
strSignature = vbcr & "My name" & vbcr & "My address" & vbcr & "My phone" 

MyItem.body strMessage  & strSignature

vbcr serves as a line breaker.
Security option and read request are optional.

hope it helps.

Yakpol