-5

I am very new to VBA. I have an already developed an Excel worksheet wherein I have an additional task as followed:

I need to create an email button and by clicking on that button, the entire worksheet should be mailed to the given recipients, also allowing me to add an attachment.

TylerDurden
  • 1,632
  • 1
  • 20
  • 30
Aakash Sehgal
  • 171
  • 1
  • 2
  • 12

1 Answers1

0

Hello Aakash Sehgal,

Sendmail()
        Dim OutApp As Object
        Dim OutMail As Object
        Dim cell As Range
        Application.ScreenUpdating = False
        Set OutApp = CreateObject("Outlook.Application")

        On Error GoTo cleanup
        For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "?*@?*.?*" And _
               LCase(Cells(cell.Row, "C").Value) = "yes" Then

                Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
                With OutMail
                    .To = cell.Value
                    .Subject = "Aakash Sehgal"
                    .Body = "Dear " & Cells(cell.Row, "A").Value _
                          & vbNewLine & vbNewLine & _
                            "Please contact us to discuss bringing " & _
                            "your account up to date"
                    'You can add files also by use:
                    '.Attachments.Add ("C:\test.txt")
                    .Send  'Or use Display
                End With
                On Error GoTo 0
                Set OutMail = Nothing
            End If
        Next cell

    cleanup:
        Set OutApp = Nothing
        Application.ScreenUpdating = True
    End Sub

Make a list of following columns in ActiveSheet:
In column A : Names of the people
In column B : E-mail addresses
In column C : yes or no ( if the value is yes it will create a mail)

the macro loop through each row on the Activesheet and if there is a E-mail-address in column B
and "yes" in column C it will create a mail with a reminder like the one below for each person.
If you have duplicate addresses in the column check out this example.

this is one example how you can make it but if you instead want to add manually the smtp is that possible too take a look here: 

Sub SMTP_Mail_SEND()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "test@test.com"
        .CC = ""
        .BCC = ""
        .From = """daniel"" <daniel@test.com>"
        .Subject = "This is a mail generated by use manually  smtp mail"
        .TextBody = strbody
        .Send
    End With

End Sub

Source: http://www.rondebruin.nl/win/s1/cdo.htm

Cheers

XsiSec

XsiSecOfficial
  • 954
  • 8
  • 20