.Speak

[MarkCBB]

Hi There,

I am currently using the following code to read some information to the user:

sub read
sheet1.range("A1").speak
end sub

however I want to change the code so that there is no range, the .speak should read from a string.

something like the following:

sub read2
dim myRead as string
myread = "Hello Mark"
myread.Speak
end sub

 

[VoG]

Hi. If you look at the Help for Speak it takes a range object. so you would need to do something like this

Sub read2()
Dim myRead As String
myRead = "Hello Mark"
With Range("Z1")
    .Value = myRead
    .Speak
    .ClearContents
End With
End Sub

 

[bensonsearch]

Hi There,

I am currently using the following code to read some information to the user:

sub read
sheet1.range("A1").speak
end sub

however I want to change the code so that there is no range, the .speak should read from a string.

something like the following:

sub read2
dim myRead as string
myread = "Hello Mark"
myread.Speak
end sub

Hi,
what am interesting post. i have learned something.

Had a play and the only way i can make a variable speak is to write it to the cell then speak the cell

 

[MarkCBB]

HI VoG,

Thanks, yeah I see, your way will work. thank you.

 

[GTO]

We just got 2010 at work, and don't have access to older at the moment, so not sure if this will help, but...

Sub exa1()
Dim strSpeakText As String
    
    strSpeakText = "Hello Mark.  How are you today?"
    
    If Talky(strSpeakText) Then
        MsgBox "Speech is new for me, but I'm not sure how new it is for vba." & vbCrLf & _
               "We just got 2010 at work.  So far, I hate the Help.", 0, ""
    End If
End Sub
    
Function Talky(ByVal Words2Speak As String) As Boolean
    Application.Speech.Speak Words2Speak
    Talky = True
End Function

Mark

 

[MarkCBB]

HI GTO,

thats great. been having alot of fun with this, got a bit side tracked. was trying to adapte this to outlook to read emails on load. but didnt work.

anyway thank you, your codce is what I was looking for.

PS: why dont you like 2010?

 

[Ruddles]

Application.Speech.Speak works fine in Excel 2007.

After I read your post, I cobbled together some code which triggers when you receive new mail in Outlook and offers you the opportunity of having each one read out to you in turn. Its fairly clunky but it seems to work...

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]    Dim mailItem As Object
    Dim intInitial As Integer
    Dim intFinal As Integer
    Dim strEntryId As String
    Dim intLength As Integer
    Dim ireply As VbMsgBoxResult
    
    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
  
    intInitial = 1
    intLength = Len(EntryIDCollection)
    intFinal = InStr(intInitial, EntryIDCollection, ",")[/FONT]
[FONT=Fixedsys]
    Do While intFinal <> 0
        strEntryId = Strings.Mid(EntryIDCollection, intInitial, (intFinal - intInitial))
        Set mailItem = Application.Session.GetItemFromID(strEntryId)
        ireply = MsgBox("Read email: """ & mailItem.Subject & """? (approx " _
               & numwords(mailItem.Body) & " words)", vbYesNo)
        If ireply = vbYes Then objExcel.Speech.Speak mailItem.Body
        intInitial = intFinal + 1
        intFinal = InStr(intInitial, EntryIDCollection, ",")
    Loop
    strEntryId = Strings.Mid(EntryIDCollection, intInitial, (intLength - intInitial) + 1)
    Set mailItem = Application.Session.GetItemFromID(strEntryId)
    ireply = MsgBox("Read email: """ & mailItem.Subject & """? (approx " _
               & numwords(mailItem.Body) & " words)", vbYesNo)
    If ireply = vbYes Then objExcel.Speech.Speak mailItem.Body
    
    objExcel.Quit
    Set objExcel = Nothing[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Function numwords(ByVal argString As String) As Long[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim sTemp As String
  
  sTemp = argString
  Do Until InStr(sTemp, "  ") = 0
    sTemp = Replace(sTemp, "  ", " ")
  Loop
  
  numwords = Len(sTemp) - Len(Replace(sTemp, " ", "")) + 1[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Function
[/FONT]

This sits in the ThisOutlookSession module in Outlook.

Mildly amusing in an annoying sort of way...

 

[MarkCBB]

Ruddles, you rock!!!!

I battle to read, and this is going to make my life sooooooo, much better, thank you so much.

this code is awesome, just tested and works like a dream. thank you so much!

 

[Ruddles]

This is a little less clunky than the first version:-

[FONT=Fixedsys]Option Explicit
Option Compare Text[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Dim myMailItem As MailItem[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim objExcel As Object
  Dim arrMailItems() As String
  Dim iReply As VbMsgBoxResult
  Dim ind As Integer
   
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
  
  arrMailItems = Split(EntryIDCollection, ",")
    
  For ind = 0 To UBound(arrMailItems)
    Set myMailItem = Application.Session.GetItemFromID(arrMailItems(ind))
    iReply = MsgBox("Read email: """ & myMailItem.Subject & """? (approx " _
               & NumWords(myMailItem.Body) & " words)" & Space(10), vbYesNo)
    If iReply = vbYes Then
      objExcel.Speech.Speak myMailItem.Body
    End If
  Next ind
     
  objExcel.Quit
  Set objExcel = Nothing
   
End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Function NumWords(ByVal argString As String) As Long[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim sTemp As String
  
  sTemp = argString
  
  Do Until InStr(sTemp, "  ") = 0
    sTemp = Replace(sTemp, "  ", " ")
  Loop
  
  NumWords = Len(sTemp) - Len(Replace(sTemp, " ", "")) + 1
  
End Function[/FONT]

I'm sure you can modify it from this point. For example, after Excel is started up and before the For loop, you could do something like this:-

[FONT=Fixedsys]objExcel.Speech.Speak "You have new mail"[/FONT]

And/or just before each MsgBox you could do something like this:-

[FONT=Fixedsys]objExcel.Speech.Speak "Message from " & myMailItem.SenderName & " with the subject " & myMailItem.Subject[/FONT]

 

[GTO]

HI GTO,

thats great. been having alot of fun with this, got a bit side tracked. was trying to adapte this to outlook to read emails on load. but didnt work.

anyway thank you, your codce is what I was looking for.

PS: why dont you like 2010?

Glad that helped a tiny bit. Ruddles' code looks very nice

It's not 2010 I dislike, just the [bleep]ing Ribbon and new 'layout' of vb help. As I think I mentioned, we just got it installed a couple of days ago, so its an annoyance to be trying to find ea little thing... Hopefully the help will grow on me. I'm not so sure I'll become quickly enamored w/the Ribbon.

Application.Speech.Speak works fine in Excel 2007.

...

Mildly amusing in an annoying sort of way...

Thank you for the info as to 2007. LOL on that last part!