Send mail form Excel using Visual Basic

Hi octi, u can use the outlook object to send the mail, then attach the Excel file as an attachment/ or body?

See this sample: Control Outlook from Excel
http://www.erlandsendata.no/english/vba/ole/controloutlook.htm

This should do it, if you don't want an attatchment just delete the relevant lines of code

Cheers

ie

Sub SendMessage(AttachmentPath)
         
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim dDate As Date
   
    On Error GoTo fail
   
    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    dDate = Now

    With objOutlookMsg
        Set objOutlookRecip = .Recipients.Add("e-mail address")
        objOutlookRecip.Type = olTo
       
        Set objOutlookRecip = .Recipients.Add("e-mail address")
        objOutlookRecip.Type = olTo
       
        Set objOutlookRecip = .Recipients.Add("e-mail address")
        objOutlookRecip.Type = olTo
       
        Set objOutlookRecip = .Recipients.Add("type e-mail address")
        objOutlookRecip.Type = olTo
       
        Set objOutlookRecip = .Recipients.Add("asaldanha@fandc.co.uk")
        objOutlookRecip.Type = olTo
       
        .Subject = "Volatility Prediction"
        .Body = "Volatility Prediction for " & Space(1) & dDate & vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High importance

        If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        End If

        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next
       
       .Send

    End With
       
    Set objOutlook = Nothing

Exit Sub

fail:
    MsgBox "E-mail has failed", vbExclamation

End Sub

Guess I should explain it.

.subject   is the subject of the e-mail
.body      is the body of the e-mail

obviously, u don't need to send it to as many people as I have...used this code only last week :)

if you need any more help pls ask

Cheers

ie

The solutions are not working because references missing from the Outlook objects. The other problem is, that if I use on other default mailer (Netscape), this samples are unusable.
Thanks,
       Br. Attila

Thanks for your answer mcbeth, but what you wrote there is a list of functions. I can't use it in excel as a macro, and I still don't konw how to send a mail.

Sorry but you can (thanks to VBA). If you don't won't to put the code into excel, create an ActiveX DLL (the code is ready to use). Add a reference to the dll and all you need is :
Sub Macroxy()
Dim xMail As New cMAIL
xMail.startmapisession
lpResult = xMail.SetRecipient(<email_address>)
xMail.MailText <BOdyText>
xMail.CreateMessage ActMsgHigh
xMail.EndMapiSession
end sub

Download the free vbSendMail from http://www.freevbcode.com/ShowCode.Asp?ID=109

mcbeth>
In the VBA of my excel the Enum type is not recognized. Is there a problem with my excel version? I use Excel 8.0.

>octi

just replace enum in functionheader and delete enums

Public Function StartMapiSession(Optional Profile As String, Optional nDialog As boolean, Optional nSession
As boolean) As Long

---------------------------------------------------
nDialog = true ( Show Dialog for Profile)
nSession = true (CreateNewSession)

mcbeth>
This types are not recognized in Excel VBA
 octi

MAPI.Session
MAPI.Message
MAPI.Recipient
MAPI.Attachment
MAPI.InfoStore

you have to reference the CDO Objects to your Excel Macro
if you are still in vba mode enter menu extra and click Reference. There you'll find Microsoft CDO for Windows 2000 Libraray. Add to code and excel vba will recognized it. btw.. it's a better way to download the file emoreau
posted. If you use vbsendmail you do not have to install any mailclient on your workstation to send mails... :-)

I have NT 4.0.
For me, your solution is better, becase I must use a certain mail client.

octi>
for NT 4 you have to reference

Active messaging Object Libraray

( its a former version
of the CDO Objects).

good luck...

mcbeth>
How can I make an instance of the cMail class from your exemple? Where is declared this? It gives me a compile error.

emoreau>
Sorry, but that solution with the free dll is not portable.
Beside this it's redundant. Why to make such dll if already exist in windows?

emoreau>
Sorry, but that solution with the free dll is not portable.
Beside this it's redundant. Why to make such dll if already exist in windows?