Need help

[azlinizam78]

Hi VBA Master,

How do i write the code for me to choose ether to add attachment or not. Or the mail will able to send without attachment. Refer below code

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = sTo
.CC = sCC
.BCC = sBCC
.Subject = sSubject
.body = sBody
.Importance = 2
.Attachments.Add UserForm1.txtAttachFile.Value
.Send
End With

[gmayor]

Given that this is an Outlook forum can we assume that you are running the code from Outlook? In which case creating a new Outlook object is neither necessary nor desirable.

Your code refers to a userform, of which you have supplied no details, but as you have a userform, it makes sense to use it for the whole process.

Let us assume a userform with five text fields txtTo, txtCC, txtSubject, txtAttachFile, and txtBody (the last is set MultiLine = True and EnterKeyBehaviour set to True)
Add a checkBox 'CheckBCC' and two command buttons btnOK and btnCancel

The code for the userform is



Option Explicit

Private Sub btnCancel_Click()
Hide
Tag = 0
lbl_Exit:
Exit Sub
End Sub

Private Sub btnOK_Click()
If txtTo.Text = "" Then
MsgBox "Enter Recipient"
txtTo.SetFocus
GoTo lbl_Exit
End If
If txtSubject.Text = "" Then
MsgBox "Enter Subject"
txtSubject.SetFocus
GoTo lbl_Exit
End If
If txtBody.Text = "" Then
MsgBox "Enter the message text"
txtBody.SetFocus
GoTo lbl_Exit
End If
Hide
Tag = 1
lbl_Exit:
Exit Sub
End Sub


The main code would then be as follows. If you don't want an attachment, don't enter the attachment path in the field.
If you want the CC to be BCC check the checkbox.


Option Explicit

Sub Macro1()
Dim oFrm As New UserForm1
Dim fso As Object
Dim OutMail As MailItem
With oFrm
.Show
If .Tag = 0 Then Exit Sub
Set OutMail = CreateItem(0)
With OutMail
.To = oFrm.txtTo.Text
If oFrm.CheckBCC.Value = True Then
.BCC = oFrm.txtCC.Text
Else
.CC = oFrm.txtCC.Text
End If
.Subject = oFrm.txtSubject.Text
.Body = oFrm.txtBody.Text
.Importance = 2
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(oFrm.txtAttachFile.Text) Then
.Attachments.Add oFrm.txtAttachFile.Text
End If
.Send
End With
End With
Unload oFrm
lbl_Exit:
Set fso = Nothing
Set OutMail = Nothing
Set oFrm = Nothing
Exit Sub
End Sub

[azlinizam78]

Given that this is an Outlook forum can we assume that you are running the code from Outlook? In which case creating a new Outlook object is neither necessary nor desirable.

Your code refers to a userform, of which you have supplied no details, but as you have a userform, it makes sense to use it for the whole process.

Let us assume a userform with five text fields txtTo, txtCC, txtSubject, txtAttachFile, and txtBody (the last is set MultiLine = True and EnterKeyBehaviour set to True)
Add a checkBox 'CheckBCC' and two command buttons btnOK and btnCancel

The code for the userform is



Option Explicit

Private Sub btnCancel_Click()
Hide
Tag = 0
lbl_Exit:
Exit Sub
End Sub

Private Sub btnOK_Click()
If txtTo.Text = "" Then
MsgBox "Enter Recipient"
txtTo.SetFocus
GoTo lbl_Exit
End If
If txtSubject.Text = "" Then
MsgBox "Enter Subject"
txtSubject.SetFocus
GoTo lbl_Exit
End If
If txtBody.Text = "" Then
MsgBox "Enter the message text"
txtBody.SetFocus
GoTo lbl_Exit
End If
Hide
Tag = 1
lbl_Exit:
Exit Sub
End Sub


The main code would then be as follows. If you don't want an attachment, don't enter the attachment path in the field.
If you want the CC to be BCC check the checkbox.


Option Explicit

Sub Macro1()
Dim oFrm As New UserForm1
Dim fso As Object
Dim OutMail As MailItem
With oFrm
.Show
If .Tag = 0 Then Exit Sub
Set OutMail = CreateItem(0)
With OutMail
.To = oFrm.txtTo.Text
If oFrm.CheckBCC.Value = True Then
.BCC = oFrm.txtCC.Text
Else
.CC = oFrm.txtCC.Text
End If
.Subject = oFrm.txtSubject.Text
.Body = oFrm.txtBody.Text
.Importance = 2
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(oFrm.txtAttachFile.Text) Then
.Attachments.Add oFrm.txtAttachFile.Text
End If
.Send
End With
End With
Unload oFrm
lbl_Exit:
Set fso = Nothing
Set OutMail = Nothing
Set oFrm = Nothing
Exit Sub
End Sub



Hi MAyor,

Thanks for your prompted replied...actually i'm using this code for userform whereby the cmdbutton still work (sending email) without attachment.