Free Microsoft Excel 2013 Quick Reference

Create Mass Email from Excel List

Hello Forum,
I have been learning more about Excel and VB over the last year and have been trying to automate more tasks related to my job. Currently I have a problem I could not find anything on the boards for, so here is my question:
I have a spreadsheet with about 2000 rows that have a name, email addresses and other information in it. How can I set up a mass email sending that would look something like this:

for i = 1 to 2000
from: myemail
to: [Cells(column A, row I).value]
Dear [cells(column B, row I).value],
You have not submitted information for your event named [cells(column C row I).value] in the [cells(column D, row I).value] term...

I could probably write some mediocre code to retrieve the information, but I have no idea how to do the output part of this task.

Thanks so much for any help.


Post your answer or comment

comments powered by Disqus
I have a script (shown below) which allows me to send a mass mailing from Excel, but I am required to click yes in Outlook stating that I am indeed trying to send a message. The help section indicates that this is due to the use of the Item.Send function. Is there a way to turn this off or automate the Yes button? Funny thing is the button will not allow you to press Yes for 5 seconds after the msg appears. I have 200+ emails to send, so that's like 15+ minutes of pushing yes...

Thanks for any ideas.

Chris

Sub SendEmail()

Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String

Set OutlookApp = New Outlook.Application

For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
Subj = "Monthly Market Survey"
Recipient = cell.Offset(0, -1).Value
EmailAddr = cell.Value

Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = Msg & "You have received this automated message because your Market Survey has not yet been logged in. Please submit to me at myemailaddy@here.com. If you have any questions or believe you received this message in error, please contact me at (123) 456-7890." & vbCrLf & vbCrLf
Msg = Msg & "Thanks." & vbCrLf & vbCrLf
Msg = Msg & "Chris"

Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Send
End With
End If
Next
End Sub

I'm currently trying to send out a mass email with a pdf. attachment to a list of names and associated email addresses I have in excel. I have VBA code which will allow me to send the email out but I'm having trouble finding the proper code for adding a pdf. attachment to the email.

I'm also currently just using plain text for my signature (removed the actual signature before posting here) but would like to be able to pull my outlook signature I have saved on my comp and insert that.

If anyone can help me out I would really appreciate it.

Here's the code I'm currently using.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String

Dim r As Integer, x As Double
For r = 1 To 100 'data in rows 1 - 100
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = "Rev1 Power Services"

' Compose the message
Msg = ""
Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & "I am with (My Company). We Specialize in hard to find, highly qualified contract personnel, such as turbine technical advisors, controls engineers, project managers, schedulers, etc., as well as the more common disciplines. We also maintain a large pool of navy nuclear trained entry level personnel." & vbCrLf & vbCrLf
Msg = Msg & "(My Company) operates with a minimal overhead, so we can provied someo of the most competitive rates in the industry, while maintaining a tight focus on quality of service on all projects served." & vbCrLf & vbCrLf
Msg = Msg & "Current clients include American Electric Power, Dominion Power, Florida Power and Light, Consolidated Edison, Silicon Valley Power, Worley Parsons, URS Group, Washington Gropu Iinternational, Shaw Group, Tampa Electric Company, Santee Cooper, CH2M Hill, and many others." & vbCrLf & vbCrLf
Msg = Msg & "Additional information can be found via our website at (company website) and in the brochure I have attached to this email. I look forward to hearing back from you and hopefully my company can be of service to you in the timely and successful completion of your current and upcoming projects." & vbCrLf & vbCrLf & vbCrLf

Msg = Msg & "Sincerely" & vbCrLf & vbCrLf
Msg = Msg & ("Signature") & vbCrLf

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End Sub

I am able to create folders named from a list in Excel from using the code:


	VB:
	
 MakeDirs() 
    For Each cell In Selection 
        MkDir "C:UsersOwnerDocumentsMainfolder" & cell.Value 
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
That works fine, but what I would now like to do is add two folders with names "high" and "low" into each of the folders created. Is there a way to modify my code to do this?

I appreciate any pointers here!

The code below is to send an email from Excel sheet to Outlook with a touch of button and it works great!!!! , Kudo to Ron De Bruin. However, I could not change the code to make Excel to open newsgroup instead of email and send messages to a
newsgroup. I have 20 students that I would like to send messages to their
Individule Newsgroup but I could not do it. This is the code that I have and
modified a little to fit my needs. But I can not have the macro to open a
newsgroup instead of email. Please help.

Thank you all in advance.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As
String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double

For r = 2 To 2 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = Cells(r, 1)

' Compose the message
Msg = Cells(r, 3)

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf,
"%0D%0A")

' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
vbNormalFocus

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:01"))
' Application.SendKeys "%s"
Next r
End Sub

--------------------------------------------------------------------------------

Send Email from Excel when cell is populated.

I have no knowledge of VB, but know that this is possible based on other threads and limited articles that I have read.

Can anyone provide me with the code to send an email out of excel when data (date) is entered into column Q or R or T of the attached spreadsheet? A prompt to send the email including text that the field has changed as well as text from column G & H would be great.

Whatever help you can provide would be greatly appreciated.

Thanks.

I am using Office 2007 with Vista on a new computer. Long tyime user of the
Office products but never experienced this issue before. When I send a file
as an attachment to an email and try to send the email from Excel, the email
will remain open for several mintues instead of simply disappearing as it did
in the past. Some times, I have to close and confirm that it was actually
sent by Outlook.

Not sure if it is a setting in Outlook or Excel (does seem to happen in our
programs that allow you to send email from their menu bar).....

Anyone have any suggestions?

I want to automatically send an email from excel when a certain value within
a cell is reached. Does anyone have a macro template to do this?? I have
other users accessing a common spreadsheet on a shared server and if they
enter data into this spreadsheet that causes a certain cell to hit a
predefined watermark that I have already setup, then I want to be able to
have excel automatically send an email to me alerting me that this watermark
or cell value has been reached.

I have already added Ron de Bruin's Excel "send email" add-in if this is
required.

Thanks.

Hi,

When I was running the macro to create CSV files from excel spreadsheet, I came across new issue. It used to work fine earlier. There are extra spaces in cell. When i just try =trim(a1), it is removing the extra spaces and working fine. But when I put it in macro and run it, it is giving an error.

I have the below text in cell. I put HTML tags around text. Otherwise it is removing extra spaces. I have also attached speadsheet with this text.

HTML Code: 


Sub SaveAllSheetsAsCSV()
    Dim wks As Worksheet
    Dim strFileFullName As String, strFileName As String, strCSVname As String, csvfile As String, lngRows As Long, lngCols
As Long
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'save the originating file name and path
    strFileFullName = ThisWorkbook.FullName
    'save the originating file name without type
    strFileName = Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".") - 1)
    
    'convert all sheets in the originating file as csv files
    'N.B. don't convert "RevisionHistory"
    For Each wks In ThisWorkbook.Worksheets()
        If wks.Name = "DatasetMetadata" Then
          csvfile = "dsmeta"
        ElseIf wks.Name = "ValueLevel" Then
                csvfile = "vlmeta"
        ElseIf wks.Name = "ComputationalAlgorithms" Then
                csvfile = "cameta"
        ElseIf wks.Name = "ExternalControlledTerminology" Then
                csvfile = "ectmeta"
        ElseIf wks.Name = "ControlledTerminology" Then
                csvfile = "ctmeta"
        Else: csvfile = wks.Name
        End If
        
        ' Remove Carriage Retruns
         For lngRows = 1 To 100
            For lngCols = 1 To 20
                If Len(wks.Cells(lngRows, lngCols)) > 0 Then
                      If (InStr(1, wks.Cells(lngRows, lngCols), vbCrLf, vbTextCompare) > 0) Or _
                      (InStr(1, wks.Cells(lngRows, lngCols), vbLf, vbTextCompare) > 0) Then
                          wks.Cells(lngRows, lngCols) = Replace(wks.Cells(lngRows, lngCols), vbCrLf, "")
                       wks.Cells(lngRows, lngCols) = Replace(wks.Cells(lngRows, lngCols), vbLf, "")
                     End If
               End If
          Next lngCols
        Next lngRows
         

        ' Remove Extra spaces
       For lngRows = 1 To 100
           For lngCols = 1 To 20
            If Len(wks.Cells(lngRows, lngCols)) > 0 Then
                wks.Cells(lngRows, lngCols) = WorksheetFunction.Trim(wks.Cells(lngRows, lngCols))
              End If
        Next lngCols
      Next lngRows

        
        If wks.Name <> "RevisionHistory" Then
              strCSVname = ActiveWorkbook.Path & _
                     "" & LCase(csvfile) & ".csv"
            wks.SaveAs Filename:=strCSVname, FileFormat:=xlCSV, CreateBackup:=False
        End If
    Next
    
    
    'Reopen the original Excel file
    Workbooks.Open strFileFullName
    
    Rem: Always set alerts and screen updating back to true
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    'Close the last csv file showing in Excel window
    ThisWorkbook.Close SaveChanges:=False

End Sub
Does any one have any idea why it is not working.

Thanks a lot for your help.

Hi all, i know it is possible to creat a shortcut on the desktop from
excel and would like to know if it is possible to create a folder from
excel as well ?
If so, could you help me some code please.

thanks in advance,

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Dear Friends..

I have a problem.. i want to send emails from Excel Sheet. Email body
should be my sheet contents... and the email should go Range("Email").value
address. (becoz receipts will be change every time...

Can anyone tell me how to write a macro for this... shall its possible??

thanks in advance

best regards,
suresh tp

Hi there,

If anyone can help with this i'd greatly appreciate it! I have a large spreadsheet with information attached in rows to individual people (names in A2:A205). I need to find a way to email the indivdual people a standard email (which i will prewrite) when the date in column BA3 becomes current. The purpose is that the person will be notified when they have to deal with their issue (the date). It is basically a prompt to eliminate the manual need of someone sorting through all the data and finding who needs to be emailed a reminder to update their row of information.

So far i have found numerous tips regarding emailing from Excel but none of them show how to set up an 'automatic' email system where as long as the spreadsheet is open it will do automatically send all the emails that need to be sent.

Any help will be greatly appreciated!

Cheers!

ana

I know this may seem weird, but I'm trying to write some code that will send
an email from excel 97 with a specified subject, but all i get is
?subject=... added to the TO field of the email. Is there any way around
this or is it just excel 97. The current method works for all versions
after 97, but ideally I want it for all versions.

Many thanks

Tysop

Hello,

I am trying to create CSV files from Excel spreadsheet for each tab. But when I convert these CSV files, text in cell is limiting to 255 characters, in otherwords it is getting truncated after 255characters. I am using Excel 2003 version.

And also when convereted these windows CSV files to UNIX csv files using the command "DOS2UNIX", some charaters becoming special characters. For exmaple, I have the actual text as "If AEENDTC ne '' then do; " and is becoming "If AEENDTC ne ćĆ then do; " Do you have any idea why it is doing this.

Thanks a lot for your help..
Sukanya

Sub CreateCSVFiles()
Application.DisplayAlerts = False

  Dim CSVName As String
  Dim CSVPath As String
  Dim NewWkb As Workbook
  Dim Wks As Worksheet
  
    CSVPath = "C:Documents and Settingsenugusxxxtest1"
    
      For Each Wks In ThisWorkbook.Worksheets
        Wks.Copy
        Set NewWkb = ActiveWorkbook
        CSVName = LCase(Wks.Name)
        NewWkb.SaveAs CSVPath & CSVName, xlCSV
        NewWkb.Close False
        Next Wks
      
End Sub


Hallo,
I have a problem, I need to create the hyperlink from Excel doc. to the
folder, but not to the existing document, is there any way to do it?
Thank you

Does anyone know how can I force Excel to show my default file location after
sending a worksheet via a email attachment?

After sending a email from excel I have to close excel and reopen to see my
files in the default file location set in the excel options.

Thanks in advance for any and all suggestions

OldCityCat

I have sent emails from excel before, but I have only used the route method. Since I do not have multiple recipients, I do not need that method.

I would like to send the email without an attachment if possible, but all the email codes that I have researched include sending an attachment.

Here is what I tried:

Private Sub
Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Range("D" & Target.Row).Value = Now
End If

If Intersect(Target, Range("E4:E339")) Is Nothing Then Exit Sub
    If LCase(Target) = "yes" Then Call SendActiveWorkbook(Range("$F$7"), Target.Offset(, 2),
Target.Offset(, 4))
    
End Sub
And this is the SendActiveWorkbook code, but I know it is wrong.

 Sub SendActiveWorkbook(Teacher As String,
Comments As String, Student As String)

    ActiveWorkbook.SendMail _

    Recipients = Teacher

    Subject = Student & Comments

End Sub
Again, it would be even better if I could send an email without the attachment.

I just need whatever was in the student and comments cell of the target with the yes to be sent. I don't need the workbook mailed.

Outlook is set up for Exchange Server. However, when we try to send an email
from Excel, the program thinks it is using an internet email account. How
can I change this? I've tried deleting the Outllook profile and recreating
it. I've also run a repair on Office 2000.
--
Deb

Is it possible to *PREVENT* the user from sending email from Excel (in Excel 2010, File | Save & Send | Send Using E-mail | Send as Attachment)?

Thanks,

Greg

Hello,
New user, and new to the forums.

Looking for help with sending emails from Excel file using Outlook, at x-date.

I see a few posts on very simular examples to my question.

If you have a little free time Leith it seems you are on top of this question.

Cheers
ROP1

I am a novice at Excel and do not know code. I attached a test workbook for suspenses I need to track. Is it possible to have a pop up reminder when a suspense date is due within a designated number of days (I set the days due like 5 or 14 days from suspense date) and is it possible to send an email from Excel to an individual(s) who is(are) responsible for the action? Thank you for any assistance.

I have a list of email addresses in Column A extending from row 1 to row
1200. I need to format these for mass email from Outlook in the format

Hello All!

Ive done quite a bit in Excel in the past, but have never set up a process that sends mail, so I'm having difficulties. So I've taken coding from http://spreadsheetpage.com/index.php...il_from_excel/ and http://forums.techguy.org/archive-bu...el-quotes.html
...and it works beautifully in excel.
-----------------
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String

Email = Cells(2, 11) & vbCrLf & "@email.com"

' Message subject
Subj = Msg & Cells(2, 12) & vbCrLf

' Compose the message
Msg = ""
Msg = ""

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

End Sub
---------------------------

Outlook loads, and its then just a case of hitting the send button, and the email just contains info in the subject line.

The workbook that this sits in is pretty much a database containing employee data in up to 10 columns and 150 rows.

I need to send an email to some of the employees in the list, and to do this, the send button would be located on each row, the macro pulling in info from the respective row (namely the email and the data in column L).

The problem is, as the macro is now, I'd need 150 slightly-different copies of the macro, which is silly.

So, what I need is the macro to be amended so that when you hit the button on the row you want, it just creates the email based on the data in that row. The end goal is to just have one macro, but 150 buttons, one on each row.

I hope thats clear....

I would include a copy of the spreadsheet if I knew how to attach files to the post...

Any help that you guys could offer would be very very appreciated!

Adam

---------------
Excel 2007

I have a list of email addresses in Column A extending from row 1 to row
1200. I need to format these for mass email from Outlook in the format
email.address@xxx.com; email.address@xxx.com....etc. so I'm sending out one
email to 1200 recipients. How do I format these addresses for this purpose in
Excel?

Hello new guy to forum and VBA for that matter... but need some help...

Ok, So I am trying to creat a looping code that will grab emails from 1 list and populate them in my code that I use to send auto send emails in lotus notes, and then pick the attachment location from another list in excel... So essentially I am looking to send 1 specific file (pdf's) to a specific person... than loop to pick the next person in the list and the next location... so the end result would be that I have sent 10 emails with attachments to 10 different recipients with a unique file attached to each. Below is my code that I sue to attach and send emails from excel through Lotus notes...

'Starting to build email
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")

If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler

'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.Subject = "Budget File"
oDoc.sendto = "example@example.com"
oDoc.body = "whatever I would like to type same message for all recipients."
oDoc.postdate = Date
oDoc.SaveMessageOnSend = True

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "H:example.pdf")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment

End Sub


No luck finding an answer? You could always try Google.