Results 1 to 3 of 3

Email send 2 attachments instead of 1

Threaded View

  1. #1
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Email send 2 attachments instead of 1

    Giday to you all

    I was trying to manupilate the codes that I found on the internet where I can attach a sheet to email in Outlook but I want to display the email before sending so that I can change the email address from time to time instead of me have to go back to VBA windows and keep changing email address in the codes.

    I'm about 90% successful but the email send 2 same attachments instead of 1.

    Sub EmailActiveSheetWithOutlook2()
    
    Dim oApp, oMail As Object, _
    tWB, cWB As Workbook, _
    FileName, FilePath As String
    
    Application.ScreenUpdating = False
    
    'Set email id here, it may be a range in case you have email id on your worksheet
    
    Mailid = "dunno@hotmail.com"
    
    'Write your email message body here , add more lines using & vbLf _ at the end of each line
    
    Body = "Please find enclosed " & vbLf _
    & vbLf _
    & "Thanks & Regards"
    
    'Copy Active Sheet and save it to a temporary file
    
    Set cWB = ActiveWorkbook
    ActiveSheet.Copy
    
    Set tWB = ActiveWorkbook
    FileName = "Temp.xls" 'You can define the name
    FilePath = Environ("TEMP")
    
    On Error Resume Next
    Kill FilePath & "\" & FileName
    On Error GoTo 0
    Application.DisplayAlerts = False
    tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
    Application.DisplayAlerts = True
    
    'Sending email through outlook
    
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
    .To = Mailid
    .Subject = "Update Message Subject here"
    .Body = Body
    .Attachments.Add tWB.FullName
        .Attachments.Add tWB.FullName
        .Display
    
    End With
    
    'Delete the temporary file and restore screen updating
    
    tWB.ChangeFileAccess Mode:=xlReadOnly
    Kill tWB.FullName
    tWB.Close SaveChanges:=False
    cWB.Activate
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
    End Sub
    The email in the codes is not my actual email address for security purpose.

    I'm sure there is only a minor configuration to do

    Thanks so much inadvance

    Last edited by unley; 01-23-2013 at 01:51 AM.
    I'm using MS Office 2013

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1