• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Send outlook mail based on Criteria & Attach Excel or PDF file

Hello,

I have created a small Macro in the attached file which will send mail the mail based on Email Id given under different column.
This macro is sending mail as of now.
My problem is
1) what code should i add so that it will send Mail Based on Column C as "YES". (Mail should only Be sent When Yes or else skip and move to next row.
2) How to add multiple Email id under To & CC. (Seperating using ; )
3) How to attached a file based on requirement to convert into PDF or Excel. under column K to S.

Thank you
Akash
 

Attachments

  • Working Send mail macro.xlsm
    27.7 KB · Views: 74
Hi,

Check if the attached macro file works for you
 

Attachments

  • Automail from Outlook through Excel Macro.xlsm
    19.1 KB · Views: 127
Hi Sathish

Thank you for you response

I am getting below error in the file attached file however.
i have given the complete path and file name as well
C:\Users\Desktop\Macro\Aug\Template - Macro.xlsm

Just to inform you i do not want a msgbox asking for Yes and NO because there will be multiple mail (like 15-20 Rows will be there with multiple recipients )will be going out. so only i have created a column for Yes when to avoid much Human intervention.

Thank you
Akash
 

Attachments

  • Error 52.jpg
    Error 52.jpg
    28.2 KB · Views: 27
  • Automail from Outlook through Excel Macro.xlsm
    19.7 KB · Views: 43
Hi,

Message box is removed in the below code

Once again check if the mentioned path name and file name are correct as I am not seeing any issue

Code:
Sub Auto_Mailer_Send()
 
    Dim msg As String
    Dim title As String
    Dim style As VbMsgBoxStyle
    Dim response As VbMsgBoxResult
   
    msg = "Do you want to send automails?"
 
    style = VbMsgBoxStyle.vbYesNo
    title = "Message"
   
    response = MsgBox(msg, style, title)
    If response = vbYes Then
 
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim Cell As Range, FileCell As Range, Rng As Range
    With Application
        .EnableEvents = False
    End With
    Set sh = Sheets("Sheet1")
    Set OutApp = CreateObject("Outlook.Application")
    For Each Cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
 
        Set Rng = sh.Cells(Cell.Row, 1).Range("E1:Z1")
        If Cell.Value Like "?*@?*.?*" And _
          Application.WorksheetFunction.CountA(Rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)
 
            strbody = "<p style='font-family:Arial;font-size:14'>Hi " & Cell.Offset(0, -1).Value & ",<br><br>" & Cell.Offset(0, 3).Value
 
            With OutMail
                .Display
               
            '    .SentOnBehalfOfName = "Co-operativemeasures@steria.co.uk" (for alternate mail id)
                .To = Cell.Value
                .CC = Cell.Offset(0, 1)
                .Subject = Cell.Offset(0, 2)
                .HTMLBody = strbody & "<br>" & .HTMLBody
 
 
                For Each FileCell In Rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell
 
                .Send
               
            End With
            Set OutMail = Nothing
        End If
    Next Cell
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
Application.ScreenUpdating = True
 
End Sub
 
Hai i requierd a macro file which enbles to send pdf file through outlook mail,

Excell file contains -To address , CC , BC ,Mail Text ,subject,attachement file name,path name of the file.

It contains more than 100 address and attachment
each receiver should get only one attachment(only pdf file) which is against his address.

Please help me, Thanks in advance.
 
Hi,

You can mention any file under the header 'Attachment'. Just ensure you mention the full path\filename with extention
 
Hi Shivraj,

Find the attached there is still few more changes needs to be made in the attached. Currently I am working on them.

Under column C (Triger) you have to put "YES" to send the mail.
and
In below code you have to give your signature path.
spath = Environ("appdata") & "\Microsoft\Signatures\Akash kothari.htm"

Hope this help.

Akash
 

Attachments

  • Working Send mail macro.xlsm
    30.9 KB · Views: 66
helloo Sir, I would like to ask u some suggestions how to send multiple emails as for one license in one cell. I have tried in one cell to add more than one but it does not work. Is there anything that I can format it in a way in vba to send multiple users emails for one particular license.. Your help and suggestions is highly appreciated sir.. tq
 
Thank You Sir. But how to define them in the vba editor because when i separate them with semicolon in the cell it only sending to the first email... tq

Hello Sir. I add .To=Range("A2").Value and then i add on many emails . yes it works. But with the specific cell range only. How to say it if i want to declare as whole column with many emails. like A. TQQQ :)

for example .To = Range("D6").Value
so it send details to only the emails in the cell D6. But How to specify for teh whole column. I modify like this .To = Range("D").Value but it dont send the notifications.. Is there a problem ?? tq sir ur help is appreciated
 
Last edited:
Hi,

It will loop through each used row and creates multiple mails

tq Sir. can i know how is it look in vba editor to get the view like this?Consider the loop sir. much appreciated.

Consider the view below is the details. how to initiate them sir by looping tqqqq :)
 

Attachments

  • excelview.PNG
    excelview.PNG
    9.1 KB · Views: 19
Last edited:
Hi,

I really can't understand your requirement. Do you require to create mails based on the input given or is it something else.
 
Hi,

I really can't understand your requirement. Do you require to create mails based on the input given or is it something else.

Sorry sir if its not clear. There is some notifications to be send to multiple users from excel. for a particular license. Consider one license will be 4 users. So when run the macro it must be able to send to all 4 emails from one cell to the users. there will be more than 100 license and with 4 users each license.

Now i dont know how to call the looping for 4 emails from a cell. .To= Range(" A2").Value this one is specific for one row. How to do for all the rows sir.??

TQ :)
 
Hi,

Check if the attached macro file works for you

Hi Sathish, are you able to tweak it by adding a criteria column that would look for the file name with Wildcard in a specific dir path?

example.. if the file criteria name is "Appleseed", the folder has a file name called Appleseed1209312.pdf , then it would attach this Pdf file?
 
Last edited:
Hi,

Check if the attached macro file works for you
Hi sir,

I need your help. I have created some 50 pdfs file. I have also created an excel stating whom to send, title, subject and path to attachment. However, I am unable to send the individual specified file to the mentioned person in one go. There is some problem in attaching the pdf files. Pl help. I am sharing the macro for your reference.

Code:
Sub SendEmail(TOID As String, subjectline As String, mailbody As String)

Dim sourcefile As String


'
' SendEmail Macro
'

Dim olapp As Outlook.Application
Set olapp = CreateObject("outlook.application")



    Dim olmail As Outlook.mailitem
    Set olmail = olapp.createitem(olmailitem)
  
    olmail.To = TOID
    olmail.Subject = subjectline
    olmail.body = mailbody
    sourcefile = Sheet1.Range()

    olmail.Attachments.Add sourcefile
  
    olmail.send
 

End Sub

Code:
Sub massmail()
row_number = 2
Do
DoEvents
row_number = row_number + 1
'' [QUOTE="Ezekielfu777, post: 246827, member: 56383"]
'' Hi Sathish, are you able to tweak it by adding a criteria column that would look for the file name with Wildcard in a specific dir path?
'' example.. if the file criteria name is "Appleseed",   the folder has a file name called Appleseed1209312.pdf  ,   then it would attach this Pdf file?
'' [/QUOTE]

Call SendEmail(Sheet1.Range("E" & row_number), Sheet1.Range("H" & row_number), Sheet1.Range("I" & row_number))

Loop Until rownumber = 51

End Sub
 
Last edited by a moderator:
Hi sir,

I need your help. I have created some 50 pdfs file. I have also created an excel stating whom to send, title, subject and path to attachment. However, I am unable to send the individual specified file to the mentioned person in one go. There is some problem in attaching the pdf files. Pl help. I am sharing the macro for your reference.
Sub SendEmail(TOID As String, subjectline As String, mailbody As String)

Dim sourcefile As String


'
' SendEmail Macro
'

Dim olapp As Outlook.Application
Set olapp = CreateObject("outlook.application")



Dim olmail As Outlook.mailitem
Set olmail = olapp.createitem(olmailitem)

olmail.To = TOID
olmail.Subject = subjectline
olmail.body = mailbody
sourcefile = Sheet1.Range()

olmail.Attachments.Add sourcefile

olmail.send


End Sub

Sub massmail()
row_number = 2
Do
DoEvents
row_number = row_number + 1


Call SendEmail(Sheet1.Range("E" & row_number), Sheet1.Range("H" & row_number), Sheet1.Range("I" & row_number))

Loop Until rownumber = 51

End Sub
 

Attachments

  • Automail Macro.xlsm
    192.4 KB · Views: 31
Back
Top