Copy Cell value from Excel and paste into current email

Status
Not open for further replies.

Cupid Stunt

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Hi,

I'm very new to VBA, I've just started working with Excel VBA and finding it incredibly useful but this is the first time I've tried anything in outlook and I'm stuck already on something I hoped was just very small and simple...

I have an excel sheet that contains a unique number that I need to change anytime someone runs any one of various Macros I have in different Excel sheets. What I now want to do is create a macro for Outlook that will open the excel file increase the cell value by 1 copy the cell contents paste this into the current active email without losing any of its contents and then save and close the excel file.

Also I need it to work in both Outlook 2007 & Outlook 2003

So far I have

Code:
Sub test()
   
  Workbooks.Open ("E:\Users\Cupid Stunt\Desktop\CurrentNumber.xlsx")
  Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Value = Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Value + 1
  Workbooks("CurrentNumber.xlsx").Save
  Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Copy
  'Workbooks("CurrentNumber.xlsx").Close
   
End Sub

This copies the data I need but I can't find a way to paste it into the active email.
I can only paste it manually
Also I've had to quote out the last line
Workbooks("CurrentNumber.xlsx").Close
because as soon as this runs I lose the data copied to the clipboard.

Any assistance anyone could offer would be most appreciated.

Thanks
 

Cupid Stunt

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Thanks for this!, i didn't even know where to start.

I've adapted so i can now paste my required text but do you know if there is way to paste into the active email without wiping out the rest of the text that is already in the email?
 

Cupid Stunt

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
In case anyone else is looking for something similar, below is what I have come up with

I used Late binding so no need to reference Word or Excel

Code:
Sub PasteCurrentNumber()

    Dim Xl As Object
    Set Xl = CreateObject("Excel.Application")
    Dim WD As Object
    Set WD = CreateObject("Word.Application")

    Dim Doc As Object
    Set Doc = Application.ActiveInspector.WordEditor
    Dim Ws As Object
    Dim Xltxt As String
 
    Xl.Workbooks.Open ("E:\Users\Cupid Stunt\Desktop\CurrentNumber.xlsx")


    Set Ws = Xl.Workbooks("CurrentNumber.xlsx").Worksheets(1)
    Xltxt = Ws.Range("A1").Value
 
    Xl.DisplayAlerts = False
 
    Xl.Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Value = _
    Xl.Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Value + 1
    Xl.Workbooks("CurrentNumber.xlsx").SaveAs ("E:\Users\Cupid Stunt\Desktop\CurrentNumber.xlsx")

    Doc.Application.Selection.TypeText Xltxt

    Xl.Workbooks("CurrentNumber.xlsx").Close
    Xl.DisplayAlerts = True

End Sub
 

Cupid Stunt

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Hi All,

my above Marco works fine in Outlook 2007 but when i tried to use on 2003 i got an error on the line

Code:
Doc.Application.Selection.TypeText Xltxt

Unfortunatly i don't have the error code.

does anyone have any ideas on how to fix to work in 2003 also?
 

Cupid Stunt

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Are you using word as the editor? That will be required.

Thank you Diane! Macro works great now.

But now i have another problem, i need to assign the Marco to a button and add it to one of the ribbons.

Which i could do when i wasn't using word as the editor but now since word is the editor when i try to add the macro to the ribbon i cannot see any Macros from Outlook as it's looking for Macros in Word.

Is there a solution to this?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You can't add button to the ribbon in 2007 - you'll need to add it to the QAT.
[DOUBLEPOST=1426767995][/DOUBLEPOST]Oops, I forgot this is for 2003. I'll have to boot a 2003 system and refresh my memory.
 

Cupid Stunt

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
I'll have to boot a 2003 system and refresh my memory.
I Know! I've had to uninstall 2010 and re-install 2003 on my home laptop to try to get this working.
Unfortuantly we have some very large Access files that don't work with anything newer than 2003 and we currently don't have time or knowledge to update them to work with anything else.

Ok so i've read that its not possible to add an outlook Macro button when you're using word as the editor in 2003 you have to re-write the macro in word and then add it as a word macro.

so i've taken the code and pasted into Word VBA editor.

This allows me to create a macro button but the code doesn't run in word.

And while typing this it just hit me and i've got it working

re-written code below works in 2003 word editor

Code:
Sub PasteCurrentNumber()

    Dim Xl As Object
    Set Xl = CreateObject("Excel.Application")
    Dim WD As Object
    Set WD = CreateObject("Word.Application")

    Dim Ws As Object
    Dim Xltxt As String
    Xl.Workbooks.Open ("E:\Users\Cupid Stunt\Desktop\CurrentNumber.xlsx")


    Set Ws = Xl.Workbooks("CurrentNumber.xlsx").Worksheets(1)
    Xltxt = Ws.Range("A1").Value
    Xl.DisplayAlerts = False
    Xl.Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Value = _
    Xl.Workbooks("CurrentNumber.xlsx").Sheets("Sheet1").Range("A1").Value + 1
    Xl.Workbooks("CurrentNumber.xlsx").SaveAs ("E:\Users\Cupid Stunt\Desktop\CurrentNumber.xlsx")

    Application.Selection.TypeText Xltxt

    Xl.Workbooks("CurrentNumber.xlsx").Close
    Xl.DisplayAlerts = True

End Sub

Thank you both very much for your guidance.

Just 1 more question

i've seen people sometimes add a couple of "tidy up" lines

like
Code:
set Xl = nothing

Is there an advantage to doing this?
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
No in most cases. In your case, the variable is declared within a function and will be set to Nothing automatically as soon as the End Sub line is executed.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
J cannot copy and paste while in list view in-cell editing Using Outlook 1
A Cannot copy this folder because it may contain private items Using Outlook 0
C Copy Move item won't work Outlook VBA and Custom Forms 2
B VBA to convert email to task, insert text of email in task notes, and attach copy of original email Outlook VBA and Custom Forms 4
Commodore Move turns into "copy" Using Outlook 3
C Copy Outlook contact field value to another field Outlook VBA and Custom Forms 1
J Copy to calendar function no longer working in outlook 365 Using Outlook 5
F Copy and replace not update contact in another pst Using Outlook 0
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
Commodore Folders always closed in move/copy items dialog box Using Outlook 3
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
geofferyh Outlook 2010 How to Copy Outlook Attachment to a Specific Folder? Outlook VBA and Custom Forms 3
S Custom Form, copy user field data to message body Outlook VBA and Custom Forms 12
R Copy Outlook Public Folders to a File Server Shared Folder Using Outlook 0
K Outlook Rules: Move a Copy Using Outlook 4
oliv- HOW TO COPY /USE FOLDERS ICONS Outlook VBA and Custom Forms 2
E Copy e-mail body from outlook and insert into excel Outlook VBA and Custom Forms 3
B Copy/Move Exchange inbox to Pop inbox Using Outlook 4
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1
O Copy mails from many subfolders to 1 foldr Using Outlook 2
K ind specific Subject line from outlook and copy the content of the email body to exce Outlook VBA and Custom Forms 0
K How to find specific header and copy the mail body Using Outlook 0
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
G Copy Contact field to Appointment Custom Form Field Outlook VBA and Custom Forms 2
G How to Copy Multi Select Listbox Data to Appointment Outlook VBA and Custom Forms 3
Carrie Dickey Outlook 2016 created two calendars titled Calendar1 - appear to be a copy Using Outlook 2
P How to copy and append data from Outlook 2016 message into Excel 2016 workbook Using Outlook 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
M How to keep reccurence during copy tasks to calendar? Using Outlook 1
Diane Poremsky Copy New Appointments to Another Calendar using VBA New Slipstick.com Articles 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0
C Copy Task to Non-Microsoft PIM "Rainlendar" Using Outlook 0
G VBA Copy draft email to a new email - attachments not copided Using Outlook 7
C Copy email to excel runtime error 5020 Using Outlook 5
I Copy email from folder to folder - FAILS Using Outlook 5
M Copy new appointments created in multiple shared calendars to another exchange calendar Outlook VBA and Custom Forms 1
Q Why can't I copy image with embedded hyperlink from email to Word Using Outlook 0
I How to make a copy of a task Using Outlook 8
F copy data in Custom Field to other folder Outlook VBA and Custom Forms 2
A Outlook: copy & paste Outlook VBA and Custom Forms 9
V Copy and paste body and subject and send multiple emails Outlook VBA and Custom Forms 3
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0
joflo copy contents from outlook to excell - please help. Using Outlook 4
L Copy email body fields to excel Using Outlook 0
G outlook 13 copy & paste without losing formatting Using Outlook 1
C How to copy same text header to multiple emails with custom text column Using Outlook 10
T Macro to find contacts by category and copy them to another folder Outlook VBA and Custom Forms 15
A Macro to Copy Data from Outlook to Excel Spreadsheet Using Outlook 2

Similar threads

Top