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.