I am using the following code to create an email message with a workbook as an attachment;

Sub email(Optional what As String) 
     'http://www.excelforum.com/excel-programming/820622-listbox-to-delete-data.html
     'maczaq
    Application.ScreenUpdating = False 
    Dim addr As String 
    With ListBox1 
        For i = .ListCount - 1 To 0 Step -1 
            addr = "" 
            Select Case what 
            Case Else: If .Selected(i) = True Then addr = "a" & i + 1 & ":b" & i + 1 
            End Select 
            If addr <> "" Then 
                Dim OutApp As Object 
                Dim OutMail As Object 
                Dim strto As String, strcc As String, strbcc As String 
                Dim strsub As String, strbody As String 
                Dim sAttachments As String 
                 
                On Error Resume Next 
                 
                 
                 
                sAttachments = (ThisWorkbook.Path & "\Survey.xlsm") 
                 
                If ListBox1.Selected(i) Then 
                    strto = ListBox1.List(i, 1) 
                    strsub = ListBox1.List(i, 4) 
                    strdate = Format(ListBox1.List(i, 3), "dd/mm/yy hh:mm") 
                End If 
                 
                Set OutApp = CreateObject("Outlook.Application") 
                OutApp.Session.Logon 
                Set OutMail = OutApp.CreateItem(0) 
                 
                strto = strto 
                strcc = "" 
                strbcc = "" 
                strsub = strsub 
                strbody = "Dear Colleague," 
                 
                 
                 
                With OutMail 
                    .To = strto 
                    .CC = strcc 
                    .BCC = strbcc 
                    .Subject = "User Survey - " & "Transaction ID: " & strsub 
                    .Body = strbody 
                    .Attachments.Add sAttachments 
                     '.Send
                    .display 
                     
                End With 
                 
                Set OutMail = Nothing 
                Set OutApp = Nothing 
                Set Employee = Nothing 
            End If 
        Next i 
    End With 
     
     'Refresh list
    With UserForm2 
        Unload Me 
    End With 
    Application.ScreenUpdating = True 
End Sub
What I am trying to do is open the attachment in the background, (without it being visible), put the value of 'strsub' in the cell Q9 on sheet1, then save the attachment with 'strsub' as the last part of the filename, attach it to the email then delete the file.

It's also posted elsewhere: http://www.thecodecage.com/forumz/mi...post1055005157 and http://www.mrexcel.com/forum/excel-q...ttachment.html