+ Reply to Thread
Results 1 to 4 of 4

Creating email from seperate data in seperate cells

  1. #1
    Registered User
    Join Date
    07-10-2014
    Location
    NJ, America
    MS-Off Ver
    2013
    Posts
    6

    Creating email from seperate data in seperate cells

    I have to create an email that consists of data from the first name, the last name, an ID number, and then add "@-----.com" The email will appear every 17th row in column B. the information it needs is also in column B. Then it needs to repeat. The email must be the first 2 characters from first name, skip the first character of last name and take the rest, and the last 4 digits of ID. Finally, it needs to add the, "@------.com" and display it on B 17. This is what I have so far:

    Sub parse()

    Dim x, n, y, z
    n = 11 ' this sets the line to count from in B for first name
    y = 9 'this sets the line to count from in B for lastname
    z = 13 'this sets the line to count from in B for Id
    For x = 17 To Range("A65536").End(xlUp).Row + 17 Step 17 'this loops the code every 17th line
    Range("B" & x).Value = Left(Range("B" & x - n).Value, 2).Value & Range("B" & x - y).Value & Right(Range("B" & x - z).Value, 4).Value & "@live.hccc.edu"
    Next x

    End Sub

    I keep getting Object Required and Error 424, object required. Any advice? :/ Thank you for any help.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Creating email from seperate data in seperate cells

    You have extra .Value after the paren of your Left and Right functions:

    Please Login or Register  to view this content.
    This code will work - whether it gives you the desired result or not ......

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-10-2014
    Location
    NJ, America
    MS-Off Ver
    2013
    Posts
    6

    Re: Creating email from seperate data in seperate cells

    That worked 99.99999%! All I need to know is how to skip the first character when taking the info from y. This is the last name. I need it to skip the first character is all and take the rest of that line. If you know that it would be perfect. Thank you for this though.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Creating email from seperate data in seperate cells

    Replace

    Range("B" & x - y).Value

    with

    MID(Range("B" & x - y).Value,2,LEN(Range("B" & x - y).Value))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Matching Cells from two seperate sheets of excel data while creating a third sheet
    By TobyJoel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2012, 04:29 PM
  2. Seperate Values in single cell seperated by [alt+enter] into seperate cells
    By Coquito in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2012, 04:24 PM
  3. Creating an array from several seperate cells...
    By levo_redkid in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-07-2008, 09:51 AM
  4. [SOLVED] How do I seperate data from a pivot into seperate worksheets?
    By Shannon in forum Excel General
    Replies: 5
    Last Post: 08-25-2005, 02:05 AM
  5. Replies: 3
    Last Post: 03-30-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1