setting address in "mail as attachment" macro

[Alan D.]

I'm trying to write a simple macro that will email a completed Excel-based
form to a designated email address. The macro as it sits so far -

Sub MailFile()
'
' MailFile Macro
'
Application.Dialogs(xlDialogSendMail).Show
End Sub

opens the mail form with the file attached just fine, but does not input the
designated email address. Can someone help me fill in the blank, as it were.
Thanks!

Alan D.

 

[JDahlgran]

Alan;
I use a piece of code to get out batch e-mails. Although mine com
from a database, this might give you a hint.

Dim objOutlook As Outlook.Application
Dim objMailItem As Outlook.MailItem
Dim adrToAddress As String

adrToAddress = recSelectL![C_EMAIL]
Set objOutlook = New Outlook.Application
Set objMailItem = objOutlook.CreateItem(olMailItem)
With objMailItem
.To = adrToAddress 'recipient's address
.Subject = strSubject & " enter a subject." 'subject goes here
.Body = "body of message" ' message goes here
'.Attachment = "c:\path\file.txt" ' attach any files here
'Debug.Print adrToAddress
.Send
End With
End If

JDahlgra

 

[Alan D.]

When I try to substitute your code for my existing line (substituting an
email address for the "reSelectL![C_EMAIL]" text), I get a "user-defined
type not defined error on the "Dim objOutlook As Outlook.Application" line.
I'm a VB newbie, so I don't have a clue what this is telling me. Any help?

Alan D.

 

[kedarkulkarni]

hi
set a reference to
Microsoft Outlook Library XX
by tools -> references..
should work fine

 

[Alan D.]

I'm afraid I'm not clear on these instructions. I don't find a
"Tools=>References" path in either Excel (2000) or Outlook. Also, as my
intention is to also allow access to this form over the internet, I'm
wondering if defining the mail client application as Outlook might be a
litttle restrictive. Is there any other way to generate a more generic
email, like with a mailto hyperlink, with the Excel form - in whatever it's
current state of completion - attached? Thanks.

Alan D.

 

[Alan D.]

P.S. I did just check out the recent thread with the macro suggestion by
Ron de Bruin, which is pretty close to what I'm going for, except that I
would like to have the email window remain open (so that the subject field
can be modified) instead of having it shot out directly.

------

I'm afraid I'm not clear on these instructions. I don't find a
"Tools=>References" path in either Excel (2000) or Outlook. Also, as my
intention is to also allow access to this form over the internet, I'm
wondering if defining the mail client application as Outlook might be a
litttle restrictive. Is there any other way to generate a more generic
email, like with a mailto hyperlink, with the Excel form - in whatever it's
current state of completion - attached? Thanks.

Alan D.

------