Daniel Campos
New Member
- Joined
- Apr 11, 2016
- Messages
- 37
Hello everyone!
This is my first post here and I'd really appreciate some help with my work. I just recently started learning Excel VBA on my own and after some research I got here and decided to ask a question about a problem I'm facing.
One of the things I have to do is to reply coworkers informing about money orders sent for their clients. I have a pre-formatted text and I just need to add the number of the order, which I usually place in the Sheet "OPE", cell "F10". I want to do this in the click of a buttom, like many things I managed to do so far, and I need the text to be selectable so I can copy it and paste in the e-mail to send.
So I tought of adding a Textbox with some predefined text in it, that fill the form with the number of the order itself. This is what I managed to do:
- Create the Textbox the size I need;
- Make the Textbox be filled with a predefined text;
But I'm facing a big wall and I think if I learn how to climb it I'll learn a lot about VBA coding and coding in general. These are the problems I'm facing and want to learn how to solve:
- I still cannot make the value in the cell "F10" be copied and past to the predefined position in the format Q_Q;
- I still cannot make the predefined text be "natural" from the textbox: I made another sub to select and fill the textbox
- To select the textbox as stated above, I have to know the exact number of that textbox, actually set as 28, but I wanted to select the latest;
This is the code I'm using:
I appreciate in advance any help given.
This is my first post here and I'd really appreciate some help with my work. I just recently started learning Excel VBA on my own and after some research I got here and decided to ask a question about a problem I'm facing.
One of the things I have to do is to reply coworkers informing about money orders sent for their clients. I have a pre-formatted text and I just need to add the number of the order, which I usually place in the Sheet "OPE", cell "F10". I want to do this in the click of a buttom, like many things I managed to do so far, and I need the text to be selectable so I can copy it and paste in the e-mail to send.
So I tought of adding a Textbox with some predefined text in it, that fill the form with the number of the order itself. This is what I managed to do:
- Create the Textbox the size I need;
- Make the Textbox be filled with a predefined text;
But I'm facing a big wall and I think if I learn how to climb it I'll learn a lot about VBA coding and coding in general. These are the problems I'm facing and want to learn how to solve:
- I still cannot make the value in the cell "F10" be copied and past to the predefined position in the format Q_Q;
- I still cannot make the predefined text be "natural" from the textbox: I made another sub to select and fill the textbox
- To select the textbox as stated above, I have to know the exact number of that textbox, actually set as 28, but I wanted to select the latest;
This is the code I'm using:
Code:
Sub GerarCorreio() Dim OPE As String
Dim box As Shape
OPE = F10
With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 10#, 10#, 665#, 450#).TextFrame
End With
Application.Wait (10)
ActiveSheet.Shapes.Range(Array("TextBox 28")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
"Prezados," & Chr(13) & Chr(13) & _
"Ordem gravada: " & OPE & Chr(13) & Chr(13) & _
"Número do MTCN:" & Chr(13) & Chr(13) & _
"Para verificação dos dados da ordem, favor consultar aplicativo CAMBIO," & Chr(13) & _
"opções 32 / 61 / Enter / Alterar prefixo da dependência para 1616" & Chr(13) & _
"e informar o CPF do cliente." & Chr(13) & Chr(13) & _
"Para providenciar o acesso à consulta, utilizar o aplicativo ACESSO," & Chr(13) & _
"opções 01 / 21 / 11 + chave do usuário + Aplicativo: OPE / marcar X na" & Chr(13) & _
"opção 61 - Consulta ordem de pagamento." & Chr(13) & Chr(13) & _
"Para ordens com valor acima de USD 3000,00 a agência deve realizar" & Chr(13) & _
"os procedimentos descritos na IN 117 (Procedimentos) item 1.1.23" & Chr(13) & _
"e encaminhar o boleto de câmbio assinado, conferido e abonado à" & Chr(13) & _
"GECEX 1616-0 em um novo correio." & Chr(13) & Chr(13) & _
"**ATENÇÃO!**" & Chr(13) & _
"- As ordens com valor até USD 3000,00, o boleto deve ser arquivado" & Chr(13) & _
"no dossiê do cliente, não sendo necessário o envio a esta Gecex!" & Chr(13) & Chr(13) & _
"- NÃO UTILIZAR esta via para nenhum tipo de questionamento ou soli" & Chr(13) & _
"citação referente a ordem de pagamento. Pois tal ação pode ocasionar" & Chr(13) & _
"duplicidade de envio o que será de responsabilidade da agência. O" & Chr(13) & _
"modelo destina-se exclusivamente para envio de ordem ( IN 117.02)." & Chr(13) & Chr(13) & _
"ATT" & Chr(13) & _
"GECEX OPERAÇÕES BHZ" & ""
Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 13).ParagraphFormat. _
FirstLineIndent = 0
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 13).Font
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 11
.Name = "+mn-lt"
End With
Selection.ShapeRange(1).TextFrame2.TextRange.Characters(14, 1).ParagraphFormat. _
FirstLineIndent = 0
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(14, 1).Font
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 11
.Name = "+mn-lt"
End With
Selection.ShapeRange(1).TextFrame2.TextRange.Characters(15, 29).ParagraphFormat _
.FirstLineIndent = 0
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(15, 29).Font
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 11
.Name = "+mn-lt"
End With
End SubI appreciate in advance any help given.
