×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Macro

Macro

(OP)
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

RE: Macro

Yes, it is possible.

RE: Macro

(OP)
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

RE: Macro

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

RE: Macro

eit,

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

CODE

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

RE: Macro

(OP)
@ 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!

RE: Macro

eit,

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources