VBA code to send document by email

[sb003848]

In a Macro, what code would I need to use if I wish to send the document by email accroding to the following:

If "E12" = 1 the send the document by email to:

TO FIELD:
Email address found in cell "B30"

CC FIELD:
orders@email.com

BCC FIELD:
If SUM of cells "E9"+"E10"+"E11"+"E13"+"E14" equals or greater than 1, send email to abc@email.com
If cell "E12" equals 1, send email to def@email.com

What do I need to do in order to make this happen?

 

[sb003848]

Went on Google and after trying multiple things, I found my answer:

Sub SendMail()
If Application.Sum(Range("E9:E11,E13:E17")) > 0 And Range("E12") = 0 Then
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Range("B30")
.cc = abc@email.com
.Subject = "Message sent to abc@email.com"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub
End If

If Range("E12") = 1 And Application.Sum(Range("E9:E11,E13E17")) = 0 Then
Dim OutApp1 As Object
Dim OutMail1 As Object

Set OutApp1 = CreateObject("Outlook.Application")
OutApp1.Session.Logon
Set OutMail1 = OutApp1.CreateItem(0)

On Error Resume Next
With OutMail1
.To = Range("B30")
.cc = "def@email.com"
.Subject = "Message for def@email.com"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0

Set OutMail1 = Nothing
Set OutApp1 = Nothing
Exit Sub
End If

If Application.Sum(Range("E9:E11,E13:E17")) > 0 And Range("E12") = 1 Then
Dim OutApp2 As Object
Dim OutMail2 As Object

Set OutApp2 = CreateObject("Outlook.Application")
OutApp2.Session.Logon
Set OutMail2 = OutApp2.CreateItem(0)

On Error Resume Next
With OutMail2
.To = Range("B30")
.cc = "abc@email.com; def@email.com"
.Subject = "Message for abc@email.com and def@email.com"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0

Set OutMail2 = Nothing
Set OutApp2 = Nothing
Exit Sub
End If
End Sub

 

[daverunt]

Hi,

Maybe you could shorten that.

If Application.Sum(Range("E9:E11,E13:E17")) > 0 And Range("E12") = 0 Then
ccTo = "abc@email.com"
MailSubject = "Message sent to abc@email.com"
End If
 
If Range("E12") = 1 And Application.Sum(Range("E9:E11,E13:E17")) = 0 Then
ccTo = "def@email.com"
MailSubject = "Message for def@email.com"
End If
 
If Application.Sum(Range("E9:E11,E13:E17")) > 0 And Range("E12") = 1 Then
ccTo = "abc@email.com; def@email.com"
MailSubject = "Message for abc@email.com and def@email.com"
End If
 
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Range("B30")
.CC = ccTo
.Subject = MailSubject
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub

What happens if E12=0 and the Sum = 0?