How to send email from excel list

[Kiran Kurapati]

Hi Team,

I am looking for some help here. I want to send emails to list of people in an excel sheet. Body of the email will be standard to the email.

Email should be sent only to those contacts who SLA is less than or equal to 60 please suggest.

I already have a macro in excel. however I am not sure how to use Macro scheduler to run for sending the mail

Thank you,

[Marcus Tettmar]

Hi,

Loop through the Excel sheet:
http://help.mjtnet.com/article/138-tran ... or-web-app

And use the SMTPSendMail command to send the email:

https://www.mjtnet.com/manual/index.htm ... ndmail.htm

http://help.mjtnet.com/article/4-sendin ... -gmail-ssl

[Kiran Kurapati]

Hi Marcus,

Thanks for sharing the link. I am all new to this of coding and could figure was would exactly suit me. could you please be kind in coding my requirement as per the below steps.

I some have a code to run in macro scheduler... how ever it needs some tweets.


Step 1. Macro scheduler should pick up the excel from my desktop

Step 2. It has to identity the all the rows which are less than or equal to 60 from column "Q" and send the mail

Step3 . Mail subject should be from Column A1

To - Should be "J"
CC - "K"
From : "I"


Here is the Code I have

Code: Select all

VBSTART
Sub SendOutlookMail

Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)
' // Option Values for Outlook Object CreateItem( ) are:
' // 0=MailItem, 1=AppointmentItem, 2=ContactItem, 3=TaskItem
' // 4=JournalItem, 5=NoteItem, 6=PostItem

objMailItem.To = "[email protected]" ' This is the primary recipient
objMailItem.Subject = "Test Macro Scheduler VB Outlook email" ' This is the Subject 
objMailItem.Body = "This was sent using Macro Scheduler code." & Chr(13) & Chr(13) & "This is an example of a second line, double spaced." ' This is the Email message 

' // Following items are optional settings that can be used =====================
objMailItem.Recipients.Add "[email protected]" ' Append a 2nd Email recipient
objMailItem.Recipients.Add "[email protected]";[email protected] ' Append additional Email recipient ;(s);
objMailItem.CC = "[email protected]";[email protected] ' Carbon Copy name ;(s);
objMailItem.BCC = "[email protected]";[email protected] ' Blind Carbon Copy Name ;(s);
objMailItem.FlagStatus = 2 '0=None (Default), 2=Marked (Red) NOTE:1=Complete (White) cannot be used on unsent email,
objMailItem.Importance = 2 '0=Low (Blue), 1=Normal (None)(Default) 2=High (Red)
' // objAttachments.Add "Source", [Type], [Position], ["Display Name to replace default file name"]
objMailItem.Attachments.Add "C:\config.sys",olByValue, 1, "Test attached config.sys"
' // End of optional items ============================

Set objMailer = objOutlook.GetNameSpace("MAPI")
objMailer.Logon "Actual Profile Name", "Actual Password" 'Logon to Mailer
objMailItem.Send ' send method
' // Use Save method vs. Send to put email in Drafts Folder vs. Outbox/Sending
' // objMailItem.Save ' save method
objMailer.Logoff ' Logoff from Mailer

End Sub
'=======================================
VBEND

VBRun>SendOutlookMail

Label>End

I have also enclosed a sample excel file for your perusal.

Kindly help.

Many a thanks

Regards,
Kiran Kumar

[Marcus Tettmar]

There's no attachment here, but I have received your identical request and Excel file via the helpdesk and have responded there.

[toopnad]

There's no attachment here, but I have received your identical request and Excel file via the helpdesk and have responded there.

Dear Markus, duplicate your answer here, since this topic is interesting and relevant in my task.

[Dorian (MJT support)]

There's no attachment here, but I have received your identical request and Excel file via the helpdesk and have responded there.

Dear Markus, duplicate your answer here, since this topic is interesting and relevant in my task.

Please feel free to start your own thread letting us know how you need help.