Automatic Email from Macro Button

[Excelsiordave]

All,

Don;t know if this is possible but I I have the following....

A form where someone enters an email address in a pop up question box and this populates another cell.

When the questionairre is completed the person is asked to click on a button to print the form.

I would like to add another button and then when the person clicks on this, the report is forwarded to the email adress entered earlier.

Can I do this by way of a macro or is it a little more technical than that.

Thanks in advance!!

Dave.

 

[VoG]

This is a good place to start http://www.rondebruin.nl/sendmail.htm

 

[Excelsiordave]

I've used this and have the following code yet it won't select outlook.....

Any ideas.....

Sub Mail_workbook_Outlook_2()
'Working in 2000-2010
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Email
.CC = ""
.BCC = ""
.Subject = "Completed SRA"
.Body = "Please find attached"
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
wb2.Close SaveChanges:=False
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Thanks.

 

[VoG]

I think that in the Visual Basic Editor you will need to set a reference to Microsoft Outlook x.x Object Library.