+ Reply to Thread
Results 1 to 2 of 2

VB Maros ".to" = Cell range

Hybrid View

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    GLASGOW
    MS-Off Ver
    Excel 2003
    Posts
    1

    Post VB Maros ".to" = Cell range

    Hi,

    Hope you can help, would like to set a macros were address ".to" is equeal to cell value

    I.e.
    .to = cell A1
    .cc = cell A2

    this it what i got so far

    Sub OT()
    'Working in Excel 2000-2013
    'Mail a copy of the ActiveWorkbook with another file name
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = ActiveSheet.Range("D6").Value & "-" & ActiveSheet.Range("E6").Value & " " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .to = ""
    .cc = ""
    .bcc = ""
    .Subject = "Overtime Request Form"
    .Body = ""
    .Attachments.Add TempFilePath & TempFileName & FileExtStr
    'You can add other files also like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'or use .Display
    End With
    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With


    Hope this makes sense

    Many thanks,

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: VB Maros ".to" = Cell range

    Try this.
    .to = Range("A1").Value
    .cc = Range("A2").Value
    PS Can you add code tags when posting code?
    If posting code please use code tags, see here.

+ 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] A "No" response in Cell A1 populates a range of cells (A2:A10) as "N/A"
    By Dutch01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2015, 06:43 PM
  2. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  3. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  4. reference date above last cell in range displaying "1" or "2"
    By leeroyrooney in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-20-2013, 05:41 PM
  5. use variable in Workbooks("book1").Worksheets("sheet1").Range("a1"
    By Luc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2005, 04:05 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