MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Outlook


Posted by Ampleford on November 05, 2001 12:48 AM

I have written a simple macro which copies a worksheet from a workbook and emails it out via Outlook. Is there a command in VB which I can put in which creates a standard "title" for the EMail Subject field.....


Posted by Robb on November 05, 2001 2:01 AM

You don't say what code you are using, but try using
the HasRoutingSlip property - it allows you to add a subject line and message.

Any help?

Regards

Posted by Ampleford on November 05, 2001 5:28 AM

Not sure what you mean by code. I am just using Microsoft Visual Basic from Excel. I wrote the macro by simply recording my actions, then I usually add a couple of lines etc to "customise" it to my needs...

: I have written a simple macro which copies a worksheet from a workbook and emails it out via Outlook. Is there a command in VB which I can put in which creates a standard "title" for the EMail Subject field.....

Posted by Kristy on November 05, 2001 9:25 AM

I don't know about VB, but with HTML you can just make a link like this:

mailto:you@bigfoot.com?subject=Whatever you want

And whoever clicks on that will automatically have "Whatever you want" as the default subject.

Doubt it's what you're looking for, but thought I'd try.

Kristy

Posted by Kristy on November 05, 2001 9:27 AM

I don't know about VB, but with HTML you can just make a link with this:

mailto:you@abc.com?subject=Whatever you want

And whoever clicks on that will automatically have "Whatever you want" as the default subject line.

Doubt it's what you're looking for, but thought I'd try to help out what little tiny bit I can.

Kristy

Posted by Ampleford on November 05, 2001 11:17 PM

I'll try that - Many Thanks

Posted by Ampleford on November 05, 2001 11:20 PM

Still no luck

I added a line with:-

Subject = "Blah Blah"

but it never worked.

Oh jings...

Posted by Ampleford on November 05, 2001 11:25 PM

I added a line:-

Subject = "Blah Blah"

But it never picked up - Can anyone help.

Posted by Robb on November 06, 2001 12:05 AM

OK - try this code instead of the macro you recorded. You may have to
amend the sheet name and recipient to suit.

Sub Send()
Dim myfile As String
ThisWorkbook.Worksheets("Sheet1").Copy
myfile = ActiveWorkbook.Name
ThisWorkbook.Activate
If Not IsNull(Application.MailSession) Then Application.MailLogon
Workbooks(myfile).HasRoutingSlip = True
With Workbooks(myfile).RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = "someone@somewhere.com" 'OR Array("someone@somewhere.com", "someoneelse@somewhereelse.com")
.Subject = "My file send"
.Message = "Try this for size"
End With
Workbooks(myfile).Route
End Sub


Does that help?

Regards

Posted by Robb on November 06, 2001 12:06 AM

Re: Still no luck

Check my later post above