Including an Attachment in Outlook 97 mail msg from Excel

02-24-2003, 09:00 AM

NateBrei

Contributor

Join Date: Jul 2002

Location: Omaha, NE

Posts: 571

[RESOLVED] Including an Attachment in Outlook 97 mail msg from Excel

I am automating a daily file updating process. I have the process working fine. Now, I would like to automate the process of attaching that file to an email. I've got the Excel macro so it sends out a message (recipient, subject, & body), but I can't get the attachment to work. I've tried doing F1 on the .Attachment, but all I get is that Outlook VBA isn't installed & instructions as to how to install them. The trouble is, they are on my machine & I've followed the instructions, but I still get the same help info on how to install.

Can anyone give me the syntax for using the .Attachment statement in the following code? Thanks in advance.

Code:

Sub EmailTest()
    Dim objOutlook As Object
    Dim objNS As Outlook.NameSpace
    Dim objMsg As Outlook.MailItem
    Dim intChar As Integer
    
    Set objOutlook = GetObject(, "Outlook.Application")
    Set objNS = objOutlook.GetNamespace("MAPI")
    strCurrentUser = objNS.CurrentUser
    intChar = InStr(1, strCurrentUser, ",")
    strCurrentUser = Mid(strCurrentUser, intChar + 2)
    Set objMsg = objNS.Application.CreateItem(olMailItem)
    With objMsg
        .Recipients.Add "Brei, Nathan"
        .Subject = "Test Email from Excel"
        .Body = "This is a test email from an Excel application"
        'Errors on the next statement
        .Attachments = "H:\Temp\Junk.doc"
        .Send
    End With
    Set objMsg = Nothing
    Set objNS = Nothing

Nate

Last edited by NateBrei; 02-24-2003 at 10:41 AM.

02-24-2003, 10:34 AM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

Nate, a little hint use Attachments.Add "H:\Temp\Junk.doc"

__________________
-Carl

02-24-2003, 10:41 AM

NateBrei

Contributor

Join Date: Jul 2002

Location: Omaha, NE

Posts: 571

Duh, Carl...

Thanks. That shouldn't have even been worth the time to write the initial post, have you or anyone else read it, or have you respond.

Sorry for wasting everyone's time. I feel ssssoooo....stupid. Of course, like all your suggestions, it works perfectly...

Nate

03-28-2003, 06:38 AM

Tiglet

Newcomer

Join Date: Mar 2003

Location: UK

Posts: 4

Send attachment using MAPI into Groupwise

I have been trying to do exactly the same thing except using MAPI as we have Novell Groupwise. This code uses message boxes for convenience, however, the attachment doesn't actually get attached - it is only attaching a reference to the file attached. Therefore, when the recipient tries to open it, Groupwise comes up with an error. Does anybody know how to solve this?
I am running Excel 2000 & Groupwise 5.5

Thanks

Tiglet

Sub MapiSendMail()
Dim objSession As Object
Dim objMessage As Object
Dim objRecipient As Object
Dim sProfile As String
Dim sSubjPrmpt As String
Dim sTextPrmpt As String
Dim sEmailPrmpt As String
Dim sMsgTitle As String
sProfile = "Novell Default Settings"
sEmailPrmpt = "Enter valid Email Name of message recipient:"
sSubjPrmpt = "Enter the subject line for this message:"
sTextPrmpt = "Enter the text for this message:"
sMsgTitle = "Mapi Macro"
' Create the Session Object.
Set objSession = CreateObject("MAPI.session")
' Log on using the session object.
objSession.Logon profileName:=sProfile
' Add a new message object to the OutBox.
Set objMessage = objSession.Outbox.Messages.Add
' Set the properties of the message object.
objMessage.Subject = InputBox(sSubjPrmpt, sMsgTitle)
objMessage.Text = InputBox(sTextPrmpt, sMsgTitle)
' Add a recipient object to the objMessage.Recipients collection.
objMessage.Attachments.Add ("c:\temp\B4000.xls")
Set objRecipient = objMessage.Recipients.Add
' Set the properties of the recipient object.
objRecipient.Name = InputBox(sEmailPrmpt, sMsgTitle)
objRecipient.Resolve
objMessage.Send showdialog:=False
MsgBox "Message sent successfully!"
' Log off using the session object.
objSession.Logoff

03-31-2003, 07:03 AM

NateBrei

Contributor

Join Date: Jul 2002

Location: Omaha, NE

Posts: 571

This is an edited version of what I ended up using for this application... It works to add an attachment (Excel file) to an Outlook message (accessed through an Excel VBA application). In the example below, strReportFileName is the filename that the user opened through an open dialog box earlier in the app. There are two differences I can see between your code & this. 1) You have quotes around your filename, but these are needed since you are providing the string. 2) You have parentheses around your filename. I do not. Perhaps that could make a difference...

Code:

With objMsg
        .Recipients.Add "LastName, FName"
        .Subject = "Daily Repricing Report (current through " & _
            strCurrentThroughDate & ")"
        .Body = "Attached is the Daily Repricing Report (also containing " & _
            "month-to-date) current through " & strCurrentThroughDate & "."
        .Body = .Body & strSignature
        .Attachments.Add strReportFileName
        .Send
    End With

Hope this helps.
Nate

03-31-2003, 08:26 AM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

I've never used Novell Groupwise but perhaps you could use the Mapi controls instead. Mapi Session and Mapi Messages.

Code:

MAPISession1.SignOn
With MAPIMessages1
        .SessionID = MAPISession1.SessionID
        .Compose
        .RecipAddress = "wamphryi.carl@vb.com"
        .ResolveName
        .MsgSubject = "Your Subject"
        .MsgNoteText = "Your Body"
        .AttachmentIndex = 0
        .AttachmentPosition = 0
        .AttachmentPathName = "C:\tempfile.xls"
        .Show
        .Send
End With
MAPISession1.SignOff

Or you depending on your needs you may only need to use the SendMail method from Excel's Object Library. I will automatically attach the workbook and send it to whom ever you specify.

Code:

ActiveWorkbook.SendMail recipients:="Jean Selva"

__________________
-Carl