Send Attachment via E-mail (extra)

[RavosJ]

Hi all,

I was able to create a macro that allows me, with the push of a button, to send he Excel file to a certain address.

Sub Mod_SendWorkbook()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.To = "xxx@gmail.com"
.CC = ""
.BCC = ""
.Subject = Range("D7").Text
.Body = "Please check attached file, thank you."
.Attachments.Add Application.ActiveWorkbook.FullName
.Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

However when I want to fine tune it a bit more:
-I would like it to only sent out I a certain range is filled in
-To display an error message which field is missing
-To display a certain message if it was sent successfully.

I keep struggling to combine any further code with the basic one...
Any help or direction would be greatly appreciated!

Thanks in advance.

 

[DanteAmor]

Hi @★ RavosJ, welcome to the forum.


Try this:

Sub Mod_SendWorkbook()
  Dim OutlookMail As Object
  Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)
  '
  'validate fields
  If Range("D7").Value = "" Then
    MsgBox "Enter Field D7"
    Exit Sub
  End If
  If Range("D8").Value = "" Then
    MsgBox "Enter Field D8"
    Exit Sub
  End If
  '
  On Error Resume Next
  With OutlookMail
    .To = "xxx@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("D7").Text
    .Body = "Please check attached file, thank you."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Display
    If Err.Number = 0 Then
      MsgBox "sent successfully"
    Else
      MsgBox "Sent error: " & Err.Number & " Description: " & Err.Description
    End If
  End With
  Set OutlookMail = Nothing
End Sub

 

[RavosJ]

Hi Dante,

There are some issues that pop-up:
- Now it opens outlook instead of sending it directly without opening outlook + it does no send the actual email and the msgbox successfully sent pops up.
- It also cover outlook with white cells that cover the send and fields, making it impossible to send anything.

I tried playing around and amending it but it is not working.
Any further assistance people?

Much appreciated.

 

[DanteAmor]

Change
.Display

By
.Send

I'm sorry for that, in my tests I use a display to not send emails.