Creating Navigation boxes through a macro

Tempus

Board Regular
Joined
May 11, 2006
Messages
56
I am have created a form in excel along with a Macro that emails it as an attachment, to variable people. what I need is how to make it so people can attach other files to the email as well, and this has to be done all through excel.
here's what I have currently
Code:
 EmailSend Macro
' Sending Email
'
        
        
   
    
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim RecipName1 As String
    Dim RecipName2 As String
    Dim RecipName3 As String
    RecipName1 = ThisWorkbook.Worksheets("New").Range("B8").Value
    RecipName2 = ThisWorkbook.Worksheets("New").Range("B10").Value
    RecipName3 = ThisWorkbook.Worksheets("New").Range("B11").Value
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    ChDir "C:\WINNT"  'Change this line to tell it where to save
         'This line tells it what to save as
         ActiveWorkbook.SaveAs "New_Plat_Cust_Form" & ".xls"
    With OutMail
        .To = "US_Platinum_Program_Office"
        .CC = RecipName2
        .BCC = ""
        .Subject = RecipName3
        .Body = "This is an automatically generated New Platinum Customer Form  "
        .Attachments.Add ThisWorkbook.FullName
        
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        
        .Send   'or use .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing

'
End Sub

I thought about using links in the attachment, but they will need to be able to select where the attachments are coming from just like any other "normal" email attachment options.
Can anyone help with this, I'm stumped.


oh and I blatanly stole and twisted ron debruins EmailSend macro. so if there are any errors they are mine and not his.


thanks,
Tempus
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello try this:


'You can add other files also like this
Dim myfiles As Variant
myfiles = Application.GetOpenFilename(, , "Please select other file(s) to attach", , True)

If IsArray(myfiles) Then
For x = 1 To UBound(myfiles)
.Attachments.Add myfiles(x)
Next
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,710
Members
410,631
Latest member
JFORTH97
Top