PDA

View Full Version : Solved: Auto attach Worksheet to new outlook email



tomsweddy
06-23-2009, 05:54 AM
Hi,

What can I add to the following bit of my code to try and get it to attach the new workbook to an email?

Sub EmailLineManager()
Dim sSheetName As String
Dim FileName As Variant


On Error Resume Next
sSheetName = Application.VLookup(Range("K12").Value, Range("F119:G127"), 2, False)
On Error GoTo 0

If sSheetName <> "" Then

FileName = Range("E6").Value
FileName = Mid(FileName, InStr(FileName, " ") + 1) & " " & _
Left(FileName, InStr(FileName, " ") - 1) & _
", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy") & ".xls"
FileName = Application.GetSaveAsFilename(FileName, "Microsoft Excel Files (*.xls), *.xls")
If FileName <> False Then

Worksheets(sSheetName).Copy
ActiveWorkbook.SaveAs FileName

End If
Else
MsgBox "You have not selected a Contract Type", vbOKOnly + vbInformation, "Information"
End If



End Sub


Here it currerntly just auto populates the Save As box for the user, using data from the worksheet......ideally I want it to attach itself to a new outlook email message and the filename to be automatically created using the code I already have.

Thanks if you can help!!

p45cal
06-23-2009, 03:07 PM
check out the likes of
ActiveWorkbook.SendMail

If this doesn't suit there are other ways which can use more of your current Outlook settings.