Excel with outlook - attachments

**dntel123** · 09-21-2009, 12:23 PM

Hey guys,

Just having some trouble with some syntax. Basically my excel spreadsheet is sending an email out and I want the clickable button when clicked to ask which attachment should be added, so an open file dialog box.

I have written this code for the file dialog:

Sub Get_Data()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then

MsgBox "No file specified.", vbExclamation, "Duh!!!"

Exit Sub

Else

Workbooks.Open Filename:=FileToOpen

End If

End Sub

And the part of my Code that adds the recipient and attachments is:

With olMail
        .To = ActiveSheet.Range("A2")
        .Subject = "General and Specific comments attached (Word 2007 Format)"
        .Body = InputBox("Please enter the Full name of the recipient of this email", "Recipient name") & "attached to thi email are your comments regarding your coursework and exam for the module: Database Administration and management."
        .Attachments.Add (Get_Data)
        .Display '.Send
    End With

Basically, the .Attachments.Add is where I am stuck, you can put a destination path in quotes and that works fine, but I want to run my other sub so that the user can select the file to attach.

Thanks in advance for any replies, info always greatly appreciated! If anyone has an alternative method I'm open to that also!

Liam

**Leith Ross** · 09-21-2009, 12:54 PM

Hello dntel123,

You need to pass the file string to the Attachments collection object. You can do this buy changing your Sub into a Function.

Function Get_Data() As String

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then

MsgBox "No file specified.", vbExclamation, "Duh!!!"

Exit Sub

Else

Workbooks.Open Filename:=FileToOpen
GetData = FileToOpen

End If

End Function

**dntel123** · 09-22-2009, 09:15 AM

Hiya,

I have been coding a spreadsheet that can take information from within the sheet and place it into an outlook mail item.

My Code goes as follows:

Sub btnSend1_Click()

Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Dim CurrFile As String

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    ActiveWorkbook.Save

    CurrFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

    With olMail
        .To = ActiveSheet.Range("B2")
        .CC = "test@test.com"
        .Subject = "Enter your Subject here"
        .Body = ActiveSheet.Range("G2").Text & vbCrLf
        .Attachments.Add "path"
        .Display '.Send
    End With

    Set olMail = Nothing
    Set olApp = Nothing
    
End Sub

This code creates an outlook message and displays it to send. My problem is that when the code gets to:

   .Attachments.Add "path"

I want it to be a selection instead of typing the path to the file (mainly so that the spreadsheet can be moved from PC to PC).
So I need an open file dialog for which this code is written:

Sub Get_Data()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Duh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If
End Sub

This works fine but what I want to do is integrate it into the above code so that the user is asked to select the attachment to add.

So Does anybody know how to integrate my 'Get_Data' Sub procedure into the attachment.add command. or another way of doing this?

Thanks in advance!

**vijay2482** · 09-22-2009, 09:28 AM

Hi,

do you want the existing excel file to be sent in outlook email or attach someother file?

**dntel123** · 09-22-2009, 10:57 AM

Hi, thanks for your reply

I want to attach a new file, such as a word document.

I'm trying to stay away from:

.Attachments.Add CurrFile

Which would add current sheet, instead want the user to be able to browse for whichever file they want, be it on desktop or in my documents

**vijay2482** · 09-22-2009, 11:09 AM

I dont think, you will need extra code to attach other files.
once you run the macro and when the outlook is opened, you can just click on the attachment button to attach a file.
Try the below code in your sheet and once the outlook is opened, just click the attachment option and see what happens.

Sub SendWithAtt()
    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Dim CurrFile As String

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    
    With olMail
        .To = "Your Email Id"
        .CC = "Optional Email ID"
    End With

    Set olMail = Nothing
    Set olApp = Nothing
 End Sub