I've got a range of data contained in 2 columns to be sent out by email. The problem is that the data in the email looks like;
Gas Meter Reading
123456
and I want it to look like;
Gas Meter Reading 123456
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
I need to email a page from a worksheet to a series of people and am currently using the following
Worksheets("Report").Activate
ActiveWorkbook.Save
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application. ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
however, I also need the worksheet to be email to the person currently running the macro (i.e. when they email it to the others, a copy is sent to thier own inbox too), is there any way in which this can be done?
The user's email address could be formed from data in the spreadsheet, if there is an easier way to do this (i.e. email address is based on a cell in the spreadsheet).
Everything works except one thing. The time in Column C is being pasted into the email in decimal format. How can I alter this to keep the time format?
View 5 Replies View RelatedI have a format and i want to put information in the format and send email only format not all workbook . i use also outlook for Email
View 9 Replies View RelatedI have been trying to find an easier way to make sure that the email address that I have been given in a excel workbook are in the proper format before I try to use them. I have tried doing a google search on this subject but have not found the correct solution. I want to enter a function in column L that would use a regular expression for the email format and compare it to the email address in column J. If the results are true place a value of 1 in column K. If false place a value of 0 in column K.
View 2 Replies View RelatedI have a set of data listed by country, I need to add a new country to the list and I want to keep everything in alphabetical order. My problem is that adding a new series to the range anywhere but the end throws of the series formatting for my chart. The plotted points on my chart are the countries flag, so when everything shifts down a row the flags no longer represent the proper country. Is there an easy way to add the new country and keep my formatting lined up or will I need to redo the formatting for all the alphabetically lower countries?
View 5 Replies View RelatedI would like to send an email to people if there email address appears in a range. ie if a checkbox = true, then their email address will appear in B2, and the next person in B3 etc... with the code to get the '.To:' to send the email to the addresses that appear in that range.
[Code] ......
Using the below code, I want data in range with format (for ex: J2:K7) to be displayed in the email sent in the place of Email_Body1 place.
Private Sub CommandButton1_Click()
Range("H2").Value = Date
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Dim i As Long
[Code]....
I am using a code copied from Ozgrid to email a worksheet but the code doesn't seem to work?
Sub Mail_Sheet_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
. ScreenUpdating = False
End With
Set rng = Nothing
Set rng = ActiveWorkbook.ActiveSheet.PrintRange 'UsedRange
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon .......................
I have a range in an Excel worksheet named 'Request'. I want to paste this range into the body of an email and send to sepcified recipient via outlook. I tried the following code, but this does not seem to work. Am I not referencing to Outlook correctly?
Private Sub cmd_Send_Request_Click()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Dim varBody As String
Dim objdata As DataObject
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
Set objdata = New DataObject
Application.Goto Reference:="Request"
Selection.Copy
objdata.GetFromClipboard
varBody = objdata.GetText
With objmail
.To = "kully.rai@ca.com"
.Subject = "Benchmarking Request"
.body = varBody & vbCrLf & vbCrLf
.NoAging = True
.Display
End With
Set objmail = Nothing
Set objol = Nothing
End Sub
I'm trying to add a range to this email code that will then paste the information into the body of this email however instead of getting the data I need i'm getting "-1" in the body instead.
I've tried defining the range below and adding that line to the main code but it doesn't work, the code will run but i get "-1" in the body.
I've been to Ron de Bruin's site. Very helpful; I got the code below from him which sends starts another bit of successful code - email_via_Outlook. The automatic email part works fine. But it only checks the one cell (E3). I've tried getting it to look at E2:E10 but to no avail. Someone said on another forum that the line "If Target.Cells.Count > 1 Then Exit Sub" stops it from checking a range of cells. I want it to check E2 to E10. I also tried another bit of code which read E2:E10 but emailed every time the worksheet was opened; I only want one email sent for every time a line dips below 30.
View 10 Replies View RelatedSimple code for sending email from excel . i have data three worksheets and i want to send email only one sheet range a1 to f35?
View 4 Replies View RelatedI get the correct results in K1 when I have a list of email addresses in column D, but when it tries to email it states, "Unknown recipient name found in the list of recipient list. Use a valid name and try again."
Code:
Dim x As String, rng As Range, cel As Range
Dim myString As String
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)
[Code] .......
I have yet to find ANY information online relating specifically to emailing any given range in Excel via Lotus Notes. The codes that do exist out there either fail to fully copy the range or relate to attaching an excel file into a Lotus Notes Memo.
Below I have copied the code the I am working with, but I keep getting the following error: Error # 438, Object doesn't support this property or method. After making some minor tweaks, it might then give me another error, Error 424 Object Required, but the range then doesn't copy into the Lotus Notes memo at that point.
I have highlighted the area that I think the code is failing at, that being the actual act of copying/pasting the range selected into the Lotus Notes Memo. With the current code, the selected range does get fully copied into a new memo in Lotus notes, but these errors populate in Excel.
Can anyone offer any suggestions to resolve this issue? I have spent hours on this and I'm sure there are many others out there that might be able to benefit from a solution as well.
Function LotusEmailQuote() ....
I need to send an email to each of the suppliers their present inventories using Outlook. If inventory is "0", set high priority flag. Example:
Supplier Item Qty Email address
A 123 10 Johndrew@link.net
A 456 2 Johndrew@link.net
B B10 20 Reliable.xom
B B11 0 Reliable.xom
C C22 9 Cold@Cola.com
I am looking to set up a vba code for sending an email to a recipient based on whether the row AI19:AI30 shows the value 3,6,9 and i want it to send an email showing all of the 3 data all of the 6 data and all of the 9 data.
I have attached my code so far.
VB:
Private Sub Workbook_Open()
Dim w As Worksheet, c As Comment
For Each w In ThisWorkbook.Worksheets
If UCase(w.Name) <> "All Trades" Then
Select Case w.Range("AI19:AI30").Value
Case Is = 3, 6, 9
[Code]....
I've wrote a function that allows me to insert a certain range to the body of an email. This email is composed in the code, too.So far I am able to build the emails body as plain text. how to retain the cell format also (colors, font size, border styles, ...)
View 14 Replies View RelatedI have a table in excel that I would like to copy into an email and send to my co-workers on a regular basis. Somewhere on the net I have find a piece of code (I've omitted a bunch of stuff here):
View 2 Replies View RelatedI have a list of email addresses in the range K10:K33, (one address per row).
How can I use that range as the 'To' field of an email address, putting a ';' between each name?
This is what I am using at the moment but I want to amend it to do the above;
Code:
Sub Mail_Selection_Range_Outlook_Body1()
Dim rng As Range
Dim OutApp As Object
[Code]....
I need to copy a range of cells from one workbook paste into another and email that new work book.
View 3 Replies View RelatedI am trying to send as an attachment "sheet1" to a email list in "sheet2" range "A1-A50"
I looked at Ron De Bruin examples but didn't find one to email a single sheet from a list.
I looked at examples at Mr. Excel but didn't find it. I apologize if I overlooked it. If I did just point me to it.
I am wrote below code to send email using VBA:
Sub SendEmail()
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String
Dim Range1 As Range, Range2 As Range
alertsSheet = ActiveWorkbook.Name
Workbooks(alertsSheet).Activate
Sheets("STATS").Select
Issue with this code is it is selecting wrong source range while generating email.
MsgBox Range1.Address is giving results as "$A$1:$B$1,$BX$1:$CA$1" and
MsgBox Range2.Address is giving results as "$A$32:$B$38,$BX$32:$CA$38" but
MsgBox rngeSend.Address is giving results as "$A$1:$B$38"
And the email generated as output is having only the data from range "$A$1:$B$38". Is there anything wrong with the way I am specifying the source range ??
I need that the code should replace strHTMLBody with the data in Range1 and Range2. I checked the tempsht.htm file and found that this file also have the same wrong data i.e. data from range "$A$1:$B$38"
Needless to specify that I am working with data having few hidden columns and need to consider only visible data.
I want my macro to be able to mail the active workbok through outlook using the email addressses in Cell R1 and having the body of the email have the text in cell R2:R8?
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim range as range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ws.Range("R1:R8").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = ws.Range("R2:R8").Value
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.
I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.
I have the following:
[C1] My dynamic email subject
[C5:C10] My dynamic email body message
My recipient is always same "burak@burak.com"
Now, I need a VBA code, that will automatically open a blank new outlook email window fill the fixed recepient fill the dynamic subject and body of the message from a range in my workbook.
Then just stop there so that I can attach some different files each time and send myself.
I have searched high and low on the forum and can not seem to find a solution to my problem. Here is my problem (besides being new to Lotus Notes): I need a button on my excel worksheet that gets information from Column H in the attached spreadsheet and puts all the emails in the To: (could be >than 100 email addresses) Field in Lotus Notes. I would like to have the subject read "New Email from Your Basin Coordinator" and have the CC list come from a second worksheet (CC).
View 4 Replies View RelatedI have around 30k data. which is in invalid format.
Ex: 12987654321vinay kk 876543219
32567456789 kkccjhg fo 345678921
I want to convert this into correct format as below with start letter from
MOD, 987654321,, vinay, kk,87654219
MOD,567456789, , kkccjhg, fo,345678921
I want to know which are formulas I have to use to get this info in correct format.