Create Email, Font Manipulation.
Sep 17, 2009Im Creating a email based on useform control values. Here is a part extract of how im creating it.
View 4 Replies
ADVERTISEMENT
Using String Manipulation To Create And Change Wroksheet Name.
Dec 5, 2008i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet()
Dim nm, i As Long
nm = Worksheets(Worksheets.Count).name
i = 1
Sheets.Add
Do While WorksheetExists(Left(nm, Len(nm) - 1) & i)
i = i + 1
Loop
ActiveSheet.name = Left(nm, Len(nm) - 1) & i
End Sub
Font Formatting In Email Sent With Vba
Feb 22, 2007I have an automatic email sent from excel. The body of the email is generated from the values of cells within excel, but I want some change of formatting (i.e. some font in bold, some in red etc.). so for example, I need the value of range c5 to be presented in bold - is there any simple way to do this?
Range("C5").Value & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Range("D5").Value & Chr(10) & _
Range("C7").Value & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Range("D7").Value & Chr(10) & _
Range("C9").Value & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Range("D9").Value & Chr(10) & Chr(10) & _
Range("C13").Value & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Range("D13").Value & Chr(10) & _
Range("C15").Value & Chr(9) & Chr(9) & Range("D15").Value & Chr(10) & Chr(10) & _
Range("C17").Value & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Range("D17").Value & Chr(10) & _
Range("C19").Value & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & Range("D19").Value & Chr(10) & _
Range("C21").Value & Chr(9) & Range("D21").Value & Chr(10)
Excel 2010 :: Select Email Font In VBA
Oct 31, 2011I can create emails with Outlook in VBA (Excel 2010), but can't find how to select the font, font size and font colour I want.
View 9 Replies View RelatedHow To Change Font Size In VB Generated Email Through Outlook
Nov 22, 2013I have this code as seen below that sends an email.
I would like to change the font size of - mymail.body=
How can I modify this existing code to achieve that.
Code:
Set myOlApp = CreateObject("Outlook.Application")
Set mymail = myOlApp.CreateItem(olMailItem)
mymail.Subject = strSubject
mymail.Body = "WHAT DO YOU PUT INTO A BOX ?"
mymail.Display
mymail.ReadReceiptRequested = False
mymail.attachments.Add "P:SR.xls"
mymail.to = "email@email.com"
mymail.Send
Change The Font Style, Size And Colour In An Email
May 26, 2009I'm looking for the lines of VBA coding that will allow me to change the Font style, size and colour in an email.
How To Create A Button That Send Email
Aug 30, 2012Anyway if its possible...i need to create a button in my worksheet, such that when one clicks it, it'll compose an email thru Outlook; outputting a specific range as the body of the email.
How do i do this?
Create Email In Lotus Notes
Jun 2, 2014I have an excel file that will be downloaded by multiple users and saved locally. I was wondering if there was a way to build code into the file so that everytime it is saved an email would be created in lotus notes from the user to me with a copy of the file attached. Is this possible?
View 2 Replies View RelatedHow To Create Outlook Email And Attach
Jun 10, 2014I'm generating a letter (Word doc) and populating bookmarks with data on a worksheet using the following code which is assigned to a Button and it works a dream (in terms of opening the Doc and populating the bookmarks with whatever data is in the relevant cells on the worksheet):
Sub test()
Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "location of the letter.doc"
[code]......
However, what I now need to do is Create an Outlook Item (email) with a Standard Subject and send to an email address that will be on the same worksheet (in cell M2 for instance) and with a standard Body.
I dont really need to open the Word doc, like it does at present, but I do need the Bookmarks contained within it to be populated with the source data contained within the Worksheet and subsequently have it attached to the email fully populated - maybe I need to 'close' / 'save as & close' the Doc post generation?
Formula To Create Email Address
Mar 21, 2007a formula to create an email address. The person's name is in cell A1. The email addresses are all the last name + first initial followed by @ and the domain name. For instance, if name is John Doe, the email address would be doej@emaildomain.com.
The problem is, some of the names in the list contain middle initials, so I can't figure out a reliable way to discern the last name.
I know I can create a custom function in VBA using InStrRev to separate the last name, but if there is a way to do it using the functions already available in Excel, I'd rather do that.
Create New Column With Company Name Which Is Embedded In Email Name
Jun 12, 2014I am working on a project. As part of project, emails were sent out to an experimental group for a cost survey. I have to combine data and do analysis now. I am given two excel spreadsheets (sample of both attached).
In sheet 1, column B contains email name. For instance, first email name (Row A2) is β96 '07 Bustello Third email name (Row A4) is 90-pt Panda Express '10
Based on this email name, I need to create a new column that contains only company name i.e Bustello against row A2, Panda Express against Row A4 and so on.
I thought of using βText to Columnβ function in the beginning. However, since there is no common character in all email names and no fixed width at which company names are embedded in email name I could not use it.
For now, I am doing it manually. I have 10,000 email names and it is becoming hard to do it manually.
Although I am given another sheet (sheet 2 of sample), which has two columns: Company number and Company Name, there is no common variable that I can use to do VLOOK UP therefore; I am not sure how to make use of that sheet.
Macro That Will Create & Send Standard Email
Feb 13, 2009I would like a macro that recognises the cell colour in column F if it is yellow i would like a standard email to be created & sent to the corresponding customer email address in column D. is this even possible??
How To Create Trigger Event That Opens Email When Row Is Altered
Nov 25, 2011I am trying to create a trigger event that opens an email when a particular row is altered. This all worked fine, until I tried to take it a step further and insert information from the spreadhseet into the email aswell.
I receive Argument not optional.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then
Call GenerateEmail(Target.Address)
End If
End Sub
[Code]....
Create Email Message With Content Prefilled From Excel?
Feb 1, 2013Is it possible to have a cell when selected create an auto-generated email message to a recipient in column A with information taken from other columns inserted into the email. I understand it may be a bit vague at this moment, but I'm just wondering if this would be an alley to investigate further or not.
View 1 Replies View RelatedCreate Email Draft With Cell Hyperlinks Intact
Dec 23, 2013Creating email drafts with the use of VBA in excel.
I've used some of his code to create an email draft to send a particular range within my excel spreadsheet. The trouble I'm having with it is keeping the hyperlinks within each cell in the range which will take the user to a particular website. How do I keep this formatting when the range is copied into the body of the email.
Example Cell A10 = HYPERLINK("URL","Google")
The hyperlinks are lost. How to keep these? Here is the code
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
Set rng = Range("EmailRange")
[Code] ........
Comparing Two Cells Based On Text, Font, Colour, Font Size Etc
Jun 3, 2006I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.
View 9 Replies View RelatedMacro To Create Unique ID From Email Address And Assign To Repeated Entries
Feb 10, 2014I am creating a rollup file from hundreds of excel files each has the same structure
Each file will have a dozens to hundreds of names and some files will share entries i.e Juan Alvarez might be in 10 different files
I need to create a unique numerical identifier starting at 0 for everyone based off there unique email address for each individual in the rollup file
So Juan Alvarez would need to be given the same numerical ID every time he showed up in the rollup
[Code] .....
Rollup. After filtering on "Name".
[Code] .....
VBA String Manipulation
Sep 1, 2009I need to be able to take someones name which is in a single cell in the format "First Middle Last" (could be more than one middle name) and then split it up so that it fits the following format:
Last name (must be 50 characters)
Middle name (must be 15 characters)
First Name (must be 15 characters)
I'm unsure on the right combination of operators to use to variably concatenate strings in this way. I'm confident I can figure out how to add the white spaces after a name to make it fit the character requirement but I don't know how to split the initial name up into it's 3 parts. Names longer than the character requirement will just be truncated.
Text Manipulation
Dec 30, 2009Column A
Column B
Column C
Economy
BRITZEconomy
Question
Compact
BRITZCompact
What formula do I need to use in column A to get the desired results as per Column C?
Medium
BRITZMedium
Large
BRITZLarge
Bus
BRITZBus
Bikes
BRITZBikes
4X4
BRITZ4X4
Clipboard Manipulation
Oct 6, 2007I am looping through and copying screens of mainframe data and appending them to the clipboard. Once done, I start a Notepad and paste it for future massaging. Now I need to perform a check on each screen which requires copying something from the screen, assigning to a variable, and checking it. This overwrites what I've already stored on the clipboard. Is there a way to store what I've got on the clipboard, then use the clipboard for something else, and then reload the clipboard?
View 3 Replies View RelatedData Manipulation And Weighting
May 25, 2009The data is arranged as such, there are a list of call types and in another column, there are a list of call lengths. Currently I have a COUNTIF which searches the call type column for certain call types ie BA is off peak but this only tells me how many off peak calls they have made, I need to make it tell me how many minutes of off peak calls there are.
IE this is an example
Call Types Call Lengths (minutes)
BA 1
BA 1
BA 3
BA 2
at the moment my formula would only count this as 4 but I need it to calculate it as 7. This is a very big data set containing around 900 entries per sheet.
String Manipulation With Exceptions
Jul 23, 2009I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
Date Display Manipulation
Nov 14, 2012Below I have a table with dates. The actual dates will be added in over time. I want to display the amount of days in the column btwn planned and actual but since it is subtracting a date from a blank cell, the #VALUE function is displayed. What function can I use to tell excel to keep the cell blank until a value other than #VALUE appears.
PLANNED Spud
ACTUAL Spud
Days
5/30/12 12:00 AM
5/30/12 2:30 AM
(0)
12/15/12 12:00 AM
#VALUE!
1/19/13 12:00 AM
#VALUE!
2/23/13 12:00 AM
#VALUE!
Info Manipulation Formula
May 13, 2007With my limited knowledge I am having trouble constructing formula to break the following info that I receive frequently from my credit card company into 3 columns - Date, Details, Value
21 Apr 2007 SAINSBURYS PETROL BURPHAM £10.00
Fancy String Manipulation
Aug 1, 2008I am trying to let the user call my subroutine in the following 5 ways but am lost using string manipulation -
Call FindReplaceAll(C, 123, 1.23) In Col C replace 123 with 1.23
Call FindReplaceAll(2, 123, 1.23) In Row 2 replace 123 with 1.23
Call FindReplaceAll(C:E, 123, 1.23) In Col C-E replace 123 with 1.23
Call FindReplaceAll(2:4, 123, 1.23) In Rows 2-4 replace 123 with 1.23
Call FindReplaceAll(C9:E19, 123, 1.23) In region C9-E9 replace 123 with 1.23
Here is what I am trying to do in psudo
Sub FindReplaceAll(sType As String, sFind As String, sReplace As String)
If sType does not have a colon in it then
If sType = A-IV or sType = a-iv only then
Columns(sType &:& sType).Select
If sType = 1-65000 then
Rows(sType &:& sType).Select
Else
Manipulation Cell Colours
Jun 22, 2009Is it possible to create a formula that looks at a cell (say A20) and if TRUE changes the colour of A22 to green if false to red?
View 9 Replies View RelatedFormula For Text Manipulation
Oct 25, 2006I have a file with a physician name and title in the cell as follows:
"James A. Jones, M.D."
I need a formula to reverse the last name, drop the title "M.D.", get rid of any additional periods after the middle initial and put it in upper case:
"JONES, JAMES A"
There are some names without middle initials also so this may present an additional problem.
Data Manipulation From Txt File
Jan 16, 2007I have test.txt file. The data in test.txt file so messy (there are headers and some data lines start with letter "A" and need to ignore). There's no <TAB> in data file, all are single space and <enter>. I can do nothing about data files.
Task:
Open test.txt file
SUM all product quantity, put result in xls file in A1
SUM all Total price in the txt file, put in xls file (same file) in B1
Close test.txt file
Data Manipulation & Grouped By The Name Field
Aug 4, 2009Example:............
Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.
Numeric String Manipulation Query
Dec 23, 2009I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.
What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.
The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.