Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 9th, 2006, 01:30 PM
Authorized User
 
Join Date: Oct 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Attachment

Hello,

I have a tool that allows the user to click send email and it will attach a worksheet to an email. However when the user opens up the email, the attachement does not have a file association (i.e. xls), how would I define this?

Any help would be greatly appreciated

Kind regards,

Alan

Private Sub cmd_enter_Click()

Dim i As Integer
Dim strSubject As String
Dim strMessage As String
Dim strRecipients As String
Dim details As Worksheet
Dim wbSend As Workbook

Dim objSession As MAPI.Session
Dim objNewMessage As MAPI.message
Dim objRecipient As MAPI.Recipient
Dim objAttachment As MAPI.Attachment

    ' Check the user is ok to send
    If MsgBox(Prompt:="Please click yes to sent", Buttons:=vbYesNo) = vbNo Then Exit Sub

    ' Set the subject of the e-mail
    strSubject = "New User Update"

    ' Set the text of the e-mail
    strMessage = "Please find attached update for Tool"

    ' Set the recipient names
    strRecipients = "email address"

    ' Save a tempory copy of the sheet with the correct filename
    Set details = ThisWorkbook.Sheets("Details")
    Set wbSend = Workbooks.add

    details.Copy Before:=wbSend.Sheets(1)

    FName = "C:\Documents and Settings\noblea\Desktop"
    wbSend.SaveAs Filename:=FName

    ' Close the Temp file
    wbSend.Close SaveChanges:=False

    ' Start CDO session
    Set objSession = New MAPI.Session
    objSession.Logon "", "", False, False

    ' Create a new message
    Set objNewMessage = objSession.Outbox.Messages.add
    With objNewMessage

        .Subject = strSubject
        .Text = strMessage

        ' Add recipients one by one and resolve against the directory
        i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Do Until i = 0
            Set objRecipient = .Recipients.add
            objRecipient.Name = Left(strRecipients, i - 1)
            objRecipient.Resolve
            strRecipients = Mid(strRecipients, i + 2)
            i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Loop
        Set objRecipient = objNewMessage.Recipients.add
        objRecipient.Name = strRecipients
        objRecipient.Resolve

    End With

    ' Attach the tempory file to the e-mail
    Set objAttachment = objNewMessage.Attachments.add

    objAttachment.Position = 0

Set objAttachment = objNewMessage.Attachments.add

    ' Delete the tempory copy


    ' Send the e-mail
    objNewMessage.Update
    objNewMessage.Send

    'Release memory
    Set objNewMessage = Nothing
    Set objSession = Nothing
    Set objAttachments = Nothing
    Set objRecipient = Nothing

    ' Reassuring message
    MsgBox "File Sent Successfuly"

End Sub
 
Old January 9th, 2006, 01:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Have you tried putting a proper xl filename in the FName variable?

Code:
FName = "C:\Documents and Settings\noblea\Desktop\Test.xls"
Maccas

 
Old January 9th, 2006, 01:37 PM
Authorized User
 
Join Date: Oct 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It still send 2 attachements, which have no name and no file extension. HELP!

Thanks

Alan
 
Old January 9th, 2006, 01:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

I seem to remember we've been through this before but you don't appear to be attaching the file properly. You should either try:
Code:
    ' Attach the tempory file to the e-mail
    Set objAttachment = objNewMessage.Attachments.Add

    objAttachment.Position = 0

    objAttachment.Type = CdoFileData
    objAttachment.ReadFromFile FName
    objAttachment.Source = FName
or
Code:
    ' Attach the tempory file to the e-mail
    Set objAttachment = objNewMessage.Attachments.Add _
        Name:="My Attached File", _
        Position:= 0, 
        Type:= CdoFileData, 
        Source:= FName
instead of
Code:
    ' Attach the tempory file to the e-mail
    Set objAttachment = objNewMessage.Attachments.add

    objAttachment.Position = 0

Set objAttachment = objNewMessage.Attachments.add





Similar Threads
Thread Thread Starter Forum Replies Last Post
Attachment error ... nawaray Beginning VB 6 5 March 5th, 2008 03:27 PM
Email Attachment rat07 PHP How-To 1 June 19th, 2007 01:27 PM
Mail attachment surendran PHP How-To 0 August 3rd, 2006 06:05 AM
Get the length of Attachment rekha_jsr Javascript How-To 1 January 7th, 2005 12:15 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.