Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Extract outlook message body text with VBA from Excel

Hi all,

I have a number of Outlook .msg files saved to a shared folder (ie outside of Outlook). I am trying to write some VBA in Excel that extracts the message body text from each in turn (as a string)...and then does some other stuff with the contents of the string.

I've done a similar thing with word documents but I'm struggling to 'get at' the text in the .msg files.

So far I have the code below. I like to think I'm on the right track at least, but I'm stuck at the line where I'm trying to set up a reference to the msg file. Any advice will be appreciated...

    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    
    Set MyOutlook = New Outlook.Application
    
    
    Set MyMail = '? stuck here!
    
    Dim FileContents As String
    
    FileContents = MyMail.Body
Thanks


Post your answer or comment

comments powered by Disqus
hi,

I am trying to automate lotus lotes through vba.
I want to send an email but in the body text,it should take a value from textbox inserted into Userform.
Does anyone know how can i do this?

VBA Mail merge word with data from excel.

Hello
I am newbie with this VBA. I am trying to merge my excel data into word all with VBA.
My word and excel version is 2000 9.0.2812
Please would you help me? I really apreciate any help !!.

I will explain in detail my needs.
I have a big excel table with data.
When I open this excel file and I select any cell from row 2, or row 3, or row 4 and press the macro "Ctrl+5" it should merge all the data from this row into a specific template of word which name and location is in column C, and this word document would be displayed on the screen to modify or print it.

I just need to display my report in word with the excel data FOR just ONE SINGLE ROW. That's all.

IN THE Excell file I have:

Name Surname Template Row2 Jose Martinez C:test1.doc Row3 Paula Perez C:test2.doc Row4 Celia Rodriguez C:test3.doc
Previously I have created the word documents and set the merge fields: Name, Surname, Template.

So if I have selected any column of row 2, the data to be merged is just the data in row 2 into my template test2.doc.
If I have selected any column of row 3, the data to be merged is just the data in row 3 and so on.....

I also need to know what kind of libraries must be activated from Visual basic panel.
Please, would anyone help me ?
Thanks a lot.
JOEY.

I have an excel file with multiple sheets. The first sheet has hyperlinks to
the remaining sheets in the file. But when I send this sheet as a message
body, the hyperlink references change. I would ideally want these links in
this sheet which is send as the message body, to link to the excel from which
it is generated. I would be grateful if you could suggest a way around.

Dear Helpers,

I would like to paste the content of the clipboard to the body of an outlook email using VBA.
I have copied some text and graphs from EXCEL and now need to paste this info in.
I'm sorry if this isn't the correct place to post this question, but i'm desperate.

Help please

Mike

Hi all.
I'm just wondering what I need to do to enter text into an existing Microsoft Org chart, with VBA.

I can enter the text manually and I can create the OrgChart with code, but I'd like to do the text entering with code, from values on the worksheet (Sheet1!A2:A48), sometimes there is a lot more.

It would be a bit of work to do 100's of text frames by hand.
I did a google search but no one seems to know how to do this.

Regards

Dear users,
Please help me finish this code (it has taken me forever). I am trying to create and send the following email in VBA from excel.

People the email should be sent TO is the contents of cell (C13)
[for example I have isham.nilar@uts.edu.au, isham.nilar@macquaire.com]
People the email should be sent BCC is the contents of cell (C14)

The subject of the email is the contents of cell (C15)

The email should read:
Hi,
Please (the contents of cell C16) (the contets of cell C17).
[for example I have 'Please "DEPOSIT" "$1,706.23")]
Thankyou
Isham

I have found trouble creating a valid outlook object library. Can someone please help finish my code...

Sub Send_Msg()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = "isham.nilar@uts.edu.au, isham.nilar@macquaire.com"
.Subject = (C15)
.Body = "This is an automated message from Excel. " & _
"Please (C16) " & _
Format(Range("C17").Value, "$ #,###.#0") & "."
.Display
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub

I haven't used VBA for a while (been out of work) and I have to refresh.. Any help will be truly appreciated.

Many thanks
Isham

I have tried eveything I can to do this. I am using Excel 2000 and Outlook 2003. I have a graph that I want to email out - I have Excel generating the email and attaching a workbook. But I would also like to just capture a graph from the workbook and display directly in the email message body.

I can get this to work by converting the graph to a .gif and attaching it, and even get it to display the .gif using HTMLBody (but you have to have a location accessible everyone on the distribution list to store the .gif) - but for the life of me I cannot get the graph to be embedded and display.

Is this just something that my vesions of Outlook and Excel cannot pull off?

You can import www site with Excel, but only those parts which writes in
HTML.
Java applet seems to disappear.
Is there any possibility to intercept the java message (text) with VBA

function choiceCheck() {
var nrTable=new Array("119b/05",-1);
var timeTable=new Array("07:16","09:50",-1);
var dateTable=new Array("23-06-2005","23-06-2005",-1); … so on

This part of data will not appear

Any suggestion
Thanks

Greetings and Salutations!

I am not so sure that this topic is to be handled here. That's why I posted it on a VB site, but there are no replies: http://www.visualbasicforum.com/t71424.html

So now I'll ask it here as well.

Can anyone help me with VBA code that will find an e-mail message with a specific attachment and then erase that message?

What I'll know is:

The e-address of the sender (me) and
The name of the attachment.

What I will not know, is whether the person :

uses
MS Outlook,
MS Outlook Express;
Netscape (?names?)
is on a network,
or not (standalone)

If I am to assume that most will be using the two MS products and are not on a network, can anyone help, please?

I cannot see any messages in the folder that the Export facility leads me to believe they are. (HKEY_CURRENT_USERIdentities{very long number}SoftwareMicrosoftOutlook Express)

Please help!?

Regards

Harry in South Africa

Thanks for taking the time to respond to this post. I appreciate your help.

The Problem: See attached file. I have data of variable row length presented in two columns. The data (in column b) appears as "4, Database 3, Database 4, Database 9, Database 10". I need to extract the first numeric text phrase (numbers from 1-10) into a separate column (column c) and extract the remaining text into another column (column d). The remaining text can be of variable length, but is always presented as alphanumeric text with commas if there is more than one.

Thank you!

Hi

I have the a problem for which i need some help.

Sub Send_mail()
    
    ' This code goes threw mail adresses in column B and sends a HTML template as body text to them.
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

        iConf.Load -1
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                           = "My smtp mail server"
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
    
    Range("B1").Select
    
    Do Until IsEmpty(ActiveCell)
    With iMsg
        Set .Configuration = iConf
        .To = ActiveCell.Text
        .CC = ""
        .BCC = ""
        .From = """Me"" <my@email.com>"
        .Subject = "Type subject here"
        .CreateMHTMLBody "file://C:/My template file path"
        
        .Send
    End With
    ActiveCell.Offset(1, 0).Select
    Loop

End Sub
What i need is something in the code that replaces a variable in the HTML Template [FirstName] with the Name in column A.

Any help is welcome

Steffen Thomsen

I'm looking to write a macro that can take text from one cell and set a user defined number of spaces between it along with the text from another cell. I did it manually and used the macro recorder, but it doesn't really help
ie

Sub Parse_Text()
'
' Parse_Text Macro
'

'
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=RC1&""        ""&RC2"
    Range("E5").Select
End Sub
As I want to be able to tell the macro how many spaces I want between the text.
Eventually I will write the parsed text to a text file.

Is there a way to do this with VBA? Can someone just point me in the right direction?

Loop to email one row of data to an email address
I am trying to create a macro that will send the header row (a1:p1) with a second row of data for a different email addresses. Mail being sent thru lotus. MSOffice 2007.

Email address for recipient is located in column Q. I want to create a loop (currently a counter) the will send and email to the address located in cell Qs, with the data contained on the same row A thru P.

My first obstacle was to get an email generated with data from excel and send. I found the code in this forum. I have been trying to figure out how to loop thru each row. I am attempting to use a counter. This may not be the best because I really just like to start sending data from row 2, 3 .... until no data exist. right now it is coded to send the data from rows 2 thru 5.

But. currently I have some type of code issue in red below; (run-time error '1004'application defined or object defined error). I have never coded in vba and am pretty sure it is a syntax issue.

.FieldSetText "EnterSendTo", Sheets("Sheet1").Range(Q, counter).Value

Any thoughts regarding this code? I really appreciate insight because this macro will streamline my job.

thanks in advance. love this forum.

Sub Email_Excel_Cells()

    Dim NSession As Object
    Dim NUIWorkSpace As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim Email ' add new dim
    Dim counter As Integer
    
    
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
            
    For counter = 2 To 5

  
    
        Set NDoc = NUIWorkSpace.ComposeDocument("", "", "Memo")
        Set NUIdoc = NUIWorkSpace.CURRENTDOCUMENT

        With NUIdoc
        
    
            .FieldSetText "EnterSendTo", Sheets("Sheet1").Range(Q, counter).Value
            .FieldSetText "EnterCopyTo", ""
            .FieldSetText "Subject", "Quarterly Supplier Performance " & Now
            .FieldSetText "Body", "Excel cells are pasted below this text" & vbNewLine &
vbNewLine & _
                "**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & _
                "Excel cells are pasted above this text"
                
            .GotoField ("Body")
            .FINDSTRING "**PASTE EXCEL CELLS HERE**"
            Sheets("Sheet1").Range("A1:p1").Copy        'the cells to copy and paste
            .Paste
            Application.CutCopyMode = False
              Sheets("Sheet1").Range(Cells(a, counter), Cells(p, counter)).Copy     'the cells to copy and paste
            .Paste
            Application.CutCopyMode = False
            .Send
            .Close
    
        End With
        

    Next counter
        
    Set NUIdoc = Nothing
    Set NDoc = Nothing
    Set NUIWorkSpace = Nothing
    Set NSession = Nothing
    
End Sub


Hi,

I have some code (at the end of this message) that worked with Excel 2003 to assign a formula to a text box on a chart sheet. The code does not work with Excel 2007 and from the new Excel Object Model...I cannot find references to formulas with shape objects.

The overall purpose of my code is to allow the user to automatically generate a number of chart sheets with scatter plots and formatted title blocks. The title blocks contain information on the figure number, figure title, project, date, etc that is ready to be included in a report. The text boxes on the chart sheets are linked to cells on a "Figure Information" worksheet allowing the user to enter and update information for all charts from one worksheet.

Code:
linkform(1) = "'" & "Figure Information" & "'" & "!$" 
linkform(2) = NumbertoLetter(figinfo + 1) 
linkform(3) = "$5" 
ActiveChart.Shapes("Text Box 2").Select 
tbzorder = ActiveChart.Shapes("Text Box 2").ZOrderPosition 
finallink = Join(linkform, "") 
ActiveChart.TextBoxes(tbzorder).Formula = finallink

Any suggestions? Thanks!

Well, I'm stumped again and in need of serious assistance.

I'm building an Excel add-in for creating project plans, which then are
exported to MS-Project for tracking. But I need to have the resource list
in an Excel worksheet before the plan can be built. And that's where I'm in
trouble. The rest of the add-in actually works, including the piece that
exports the plan to MS-Project (2003). But I'm stuck on the part that
automates the retrieval of the resource list from Project.

The code below shows how far I've been able to get on my own. It actually
works, every second time that it runs, and it fails every second time that
it runs. I get a message that the application server doesn't exist or is
not available. But I can't figure out what's going on, and the available
documentation is, well, excessively challenging for me (read that as I don't
know Project's object model and I can't find readable documentation). Can
somebody here straighten me out? I would be entirely grateful

Tony

Sub GetResourceList()
Dim objMSProject As MSProject.Application
Dim R As Resources
Dim curPath As String
Dim fileToOpen As Variant
Dim thisExcel As Excel.Application
Dim Temp As Long
Dim Names As String
Dim rSheet As Worksheet

curPath = CurDir

fileToOpen = Application.GetOpenFilename("Microsoft Project Files
(*.mpp), *.mpp")

If fileToOpen False Then

Set thisExcel = Excel.Application

Set objMSProject = New MSProject.Application
objMSProject.Visible = True
objMSProject.FileOpen Name:=fileToOpen
objMSProject.Projects(fileToOpen).Activate

Set R = ActiveProject.Resources

For Temp = 1 To R.Count
Names = R(Temp).Name & ", " & Names
Next Temp
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))

End If

objMSProject.DisplayAlerts = False
'objMSProject.FileCloseAll pjDoNotSave
'FileExit pjDoNotSave
Quit savechanges:=pjDoNotSave
'objMSProject.Quit pjDoNotSave

Set objMSProject = Nothing
thisExcel.Visible = True
ChDir curPath

MsgBox Names

End Sub

Well, I'm stumped again and in need of serious assistance.

I'm building an Excel add-in for creating project plans, which then are
exported to MS-Project for tracking. But I need to have the resource list
in an Excel worksheet before the plan can be built. And that's where I'm in
trouble. The rest of the add-in actually works, including the piece that
exports the plan to MS-Project (2003). But I'm stuck on the part that
automates the retrieval of the resource list from Project.

The code below shows how far I've been able to get on my own. It actually
works, every second time that it runs, and it fails every second time that
it runs. I get a message that the application server doesn't exist or is
not available. But I can't figure out what's going on, and the available
documentation is, well, excessively challenging for me (read that as I don't
know Project's object model and I can't find readable documentation). Can
somebody here straighten me out? I would be entirely grateful

Tony

Sub GetResourceList()
Dim objMSProject As MSProject.Application
Dim R As Resources
Dim curPath As String
Dim fileToOpen As Variant
Dim thisExcel As Excel.Application
Dim Temp As Long
Dim Names As String
Dim rSheet As Worksheet

curPath = CurDir

fileToOpen = Application.GetOpenFilename("Microsoft Project Files
(*.mpp), *.mpp")

If fileToOpen <> False Then

Set thisExcel = Excel.Application

Set objMSProject = New MSProject.Application
objMSProject.Visible = True
objMSProject.FileOpen Name:=fileToOpen
objMSProject.Projects(fileToOpen).Activate

Set R = ActiveProject.Resources

For Temp = 1 To R.Count
Names = R(Temp).Name & ", " & Names
Next Temp
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))

End If

objMSProject.DisplayAlerts = False
'objMSProject.FileCloseAll pjDoNotSave
'FileExit pjDoNotSave
Quit savechanges:=pjDoNotSave
'objMSProject.Quit pjDoNotSave

Set objMSProject = Nothing
thisExcel.Visible = True
ChDir curPath

MsgBox Names

End Sub

I am looking to bring data into a excel file as a string one line at a time so I can evaluate the type of data on the line. When I have found the right data I can brake the string up into the right cells. The line might have the load case, data type or loads. How do I open the text file with VBA in Excel and read the file line by line into a string?

Hello all

First of all i work with Excel 2010.
I have an excel sheet witch has some textblocks.
I want do export this textblocks via vba.

The problem is not the export.
The problem ist the formating.
in excel a cell looks like this

-------------------------------------------------
Here is some headertext

here follow some normal text with Bold and Unerline word.

Some text with address and odther infomation and bla bla bla bla bla bla

bla bla bla bla bla bla some wath bla bla
-------------------------------------------------

(Bold parts are not shown in this text)

in the export i have to write the following:
-------------------------------------------------
%BOLD_ON%Here is some headertext%BOLD_OFF%

here follow some normal text with Bold and Unerline word.

Some text with %BOLD_ON%address%BOLD_OFF% and odther %UNDERLINE_ON%infomation%UNDERLINE_OFF% and bla bla bla bla bla bla

bla bla bla bla bla bla %BOLD_ON%%UNDERLINE_ON%some wath%BOLD_OFF%%UNDERLINE_OFF% bla bla
-------------------------------------------------

Right now i do this with this code

Function
check_bold_and_underline(inpcell As Range) As Variant
    ' Test ob ganzes Feld BOLD (fnt=TRUE)
    ' ganzes Feld nicht Bold (fnt=FALSE)
    ' oder Teilweise Bold (fnt=NULL)
     fnt = inpcell.Font.Bold
    ' Test ob ganzes Feld Underline (fntu=xlUnderlineStyleSingle)
    ' ganzes Feld nicht Underline (fntu=xlUnderlineStyleNone)
    ' oder Teilweise Underine (fntu=NULL)
     fntu = inpcell.Font.Underline
     temptext = ""
     last_font = False
     last_fontu = xlUnderlineStyleNone
     If fnt = True Then
         temptext = "#BOLD#"
         last_font = fnt
     End If
     If fntu = xlUnderlineStyleSingle Then
         temptext = "#UNDERLINE (ON)#" & temptext
         last_fontu = fntu
     End If
     
     If IsNull(fntu) Or IsNull(fnt) Then
         For p = 1 To Len(inpcell.Value)
             If IsNull(fnt) Then
                 If inpcell.Characters(p, 1).Font.Bold <> last_font Then
                     If last_font = False Then
                         temptext = temptext & "%BOLD_ON%"
                         last_font = True
                     Else
                         temptext = temptext & "%BOLD_OFF%"
                         last_font = False
                     End If
                 End If
             End If
             If IsNull(fntu) Then
                 If inpcell.Characters(p, 1).Font.Underline <> last_fontu Then
                     If last_fontu = xlUnderlineStyleNone Then
                         temptext = temptext & "%UNDERLINE_ON%"
                         last_fontu = xlUnderlineStyleSingle
                     Else
                         temptext = temptext & "%UNDERLINE_OFF%"
                         last_fontu = xlUnderlineStyleNone
                     End If
                 End If
             End If
             temptext = temptext & Mid(inpcell, p, 1)
         Next p
     Else
         temptext = temptext & inpcell.Value
     End If
     check_bold_and_underline = temptext
End Function
My problem ist the time witch is used.
If the cells has 1000 characters,the routine will take 35 seconds.
With Excel 2003 the same Script takes about 10 seconds.

Any idea? how i can speed up the script or is there an other function in Excel 2010?

Best regards
Roger

hi,
i have a question to form diagram with VBA in Excel.
I have a table full with data from A1:P200. but i want to have A1:A200 in x-axis and N1:N200 in y-axis. but i couldn`t find how write to define my axis choice.
i will happy for your help.
ferda

Hi everyone,

I am a beginner with vba in excel and i have the following problem:

In a first worksheet called "Data", I have different columns with the same amounts of rows. (the ammount of rows can be variable from depending on the data imported but is the same for all the columns).

In a my worksheet called "Results", I want to create a macro that will autofill different formula (='Data!A1+'Data!B1) for all the filled up rows in the "Data" worksheet.

It might be a simple question for some of you but it will greatly help me with my summer project thank you in advance!!!

Matt

Hello,

With regards to the questions in the subject, is this possible to do? Also,
if it's so only in Outlook, can I export the files from Excel into Outlook?
And if that can happen, where do I found out how to do that? Can excel files
also be exported into a contact manager such as ACT? I think ACT has the
phone capability. If you know this is so, could you also respond to this?

Thanks Very Much!!!

Stuart

Hi

I'm having difficulty applying conditional formatting to a dataset with VBA and would really appreciate it if someone can advise what I'm doing wrong.

For some strange reason the conditional formatting formula set up by my Macro in Excel differs from the formula I instruct it to set up in in my code below:

Sub ConditionalFormat()

Dim WSA As Worksheet

Set WSA = Worksheets("Sheet1")

With WSA.Range("$B$5:$D$14")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D5>$D$2"
.FormatConditions(1).Interior.ColorIndex = 44
End With

End Sub

I've also attached the Excel workbook and a screenshot showing the actual formula set up in Excel ("=$D2>$D$2") and not ("=$D5>$D$2").

Many thanks in advance for your help.

Jacques

Does anybody in this group know enough about Goldmine to open a new email message in Goldmine with VBA commands? I have been to the
GM forums and haven't found a solution yet. I don't want to ignore my "home" forum in case there is somebody here who actually knows
how to do this. I've been reading documentation and manuals for days with no luck. Hopefully my homies (you) know how to do this.

Thanks !
--
RMC,CPA

Hi there.
I want to open text files with vba in excel using:
open filename for input as #1 ....
As long as the file is in the My Documents folder, I have no problem. When the file is in another folder and another drive, and I have to give the path of the file, I use the chdir() function. However this doesn't work. ( actaually sometimes it does and sometimes it doesn't) .

Thanks for the advise.


No luck finding an answer? You could always try Google.